0

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.

enter image description here

2
  • 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. Oct 12, 2017 at 16:03
  • HI - I have put the wrong lookup value column - it should have said A2. Oct 13, 2017 at 6:16

2 Answers 2

1

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")
1

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.

1
  • 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .