Lookup, verify and return data

davidvcolorado

I am in need of a formula that looks at a cell in sheet 1 and then locates the a corresponding cell in sheet 2 and returns a status within that row back to sheet 1.

Here is what I had started but keep falling short.

Sheet 1:

Sheet 2:

Formula: =IF(ISBLANK([VIN - Last 6]@row), "", INDEX({Chassis Check-in Range 2}, MATCH({Chassis Check-in, VIN - Last 6}, {Chassis Check-in Range 2}, 0)))

I need Sheet 1 to show the chassis status (Arrived / Departed) shown in sheet 2 based on a matching VIN.

Paul Newcome

The first field in the MATCH function should be a cell reference to the cell on the sheet containing the formula that you want to match on (looks like [VIN - Last 6]@row).

davidvcolorado

Paul,

Thanks you for the help I feel like I am getting closer but now I am getting a #NO MATCH error. Here is the latest version of my formula: =IF(ISBLANK([VIN - Last 6]@row), "", INDEX({Chassis Check-in Range 1}, MATCH([VIN - Last 6]@row, {Chassis Check-in Range 2}, 0)))

Thank you again for the help.

David

=Chris Palmer

Hi David,

Have you tried breaking your formula out in smaller steps before adding your IF(ISBLANK?

Starting with the Index Match:

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row, {ColumnWithTheValueToMatchAgainsTheCell}, 0))

If that works, then begin adding the IF

=IF(Cell="True",INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row, {ColumnWithTheValueToMatchAgainsTheCell}, 0)),"False")

Lastly, replace the "True" "False" with your desired output.

Hope this helps!

Paul Newcome

That error comes from where there is no match on the VIN. Start by double checking the correct ranges. Are there any that have leading zero(s)?

davidvcolorado

Chris,

I have been able have it return the correct "Arrived" now with the following formula:

=INDEX({Chassis Check-in Arrived @row}, MATCH([VIN - Last 6]@row, {Chassis Check-in VIN -Last 6@row}))

When I add in the IF portion that is where I get an error again.

Paul,

What do you mean by leading zero(s)?

I appreciate all the help from you both.

Paul Newcome

Do you have some entries that are (for example)

123456

and other entries that are

012345

with a leading zero?

davidvcolorado

Paul,

Yes I do have a few that start with 0.

Paul Newcome

You will need to insert a helper column on both sheets and use this column formula in both.

=[VIN - Last 6]@Row + ""

This will convert all entries into text values instead of having a mix of some text and some numerical. Then you would reference both of these columns in your formula.

davidvcolorado

Ok, thank you I really appreciate all the help.

Paul Newcome

Happy to help. 👍️