So I am looking up IDs between 2 different tabs and I need to return the value if the vlookup doesn't work instead of just a #NA.
-
Michelle, this makes no sense. The VLOOKUP() formula that you show can't work the way you indicate. VLOOOKUP() always tries to find the lookup value (12345 here, I think) in the first column of the lookup array. Here that is Column A of Sheet 2, a list of names. So the formula won't find any numbers there and will return #N/A for everything. It might be that I'm misunderstanding the way you've tried to display your data. If so, please explain it more clearly.– BandersnatchOct 12, 2017 at 16:03
-
HI - I have put the wrong lookup value column - it should have said A2.– Michelle RushbyOct 13, 2017 at 6:16
2 Answers
You could use iferror() with the value you want as the fall back.
Syntax
IFERROR(value, value_if_error)
Example
=IFERROR(A2/B2, "Error in calculation")
Not sure if your request is more simpler than it sounds or more complicated.
Why it might be simpler: If sheet 2 data already contains the correct answer, then you don't need to compare to Sheet 1 data, just get the sheet 2 data.
Why it might be complicated: If you are doing this to error check and you have to manually determine the right value then maybe syntax like this could be a start but only if names are in both sheets and no duplicates within each sheet:
=if(vlookup(table3Name,table1,2,false)=vlookup(table3Name,table2,2,false), "",vlookup(table3Name,table2,2,false))
However, if you are doing error checking, then I suggest you have three columns in the results table (in place of your one ID column) column 1 would be Table 1 ID (vlookup), column 2 would be Table 2 ID (vlookup) and column three would be the check column (if).
Again, you need to further assess your data and if there are duplicates within a table, or if names are not in both sheets, then you'll need a different syntax for the error check column.
Hope this helps.
-
Yes I am doing error checking. And there are duplicates. I will see if there is a way to have unique values by doing concatenate or something like that for my lookup values. Oct 13, 2017 at 6:14