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 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.
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 Check-in Range 1}, MATCH([VIN - Last 6]@row, {Chassis Check-in 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!
https://www.linkedin.com/in/zchrispalmer/
-
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 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.
-
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.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!