Lookup, verify and return data
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 Checkin Range 2}, MATCH({Chassis Checkin, VIN  Last 6}, {Chassis Checkin Range 2}, 0)))
I need Sheet 1 to show the chassis status (Arrived / Departed) shown in sheet 2 based on a matching VIN.
Answers

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).

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 Checkin Range 1}, MATCH([VIN  Last 6]@row, {Chassis Checkin Range 2}, 0)))
Thank you again for the help.
David

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!

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)?

Chris,
I have been able have it return the correct "Arrived" now with the following formula:
=INDEX({Chassis Checkin Arrived @row}, MATCH([VIN  Last 6]@row, {Chassis Checkin 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.

Do you have some entries that are (for example)
123456
and other entries that are
012345
with a leading zero?

Paul,
Yes I do have a few that start with 0.

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.

Ok, thank you I really appreciate all the help.

Help Article Resources
Categories
Check out the Formula Handbook template!