Trying to use a VLOOKUP as the value_if_error in an IFERROR

[ACK93]

Excel 2010 doesn't seem to want to handle this?

=IFERROR(VLOOKUP(A125,sheet1,4,FALSE),VLOOKUP(A125,sheet1,2,FALSE)).


In other words, If there is a value in column 4 of the lookup table, use it, otherwise use the value in column 2. Excel 2010 accepts the syntax. The result in the cell is the value in column 2 when there actually is a value in column 4.

 

[Hui]

Ack93


You are missing ranges


=IFERROR(VLOOKUP(A125,sheet1,4,FALSE),VLOOKUP(A125,sheet1,2,FALSE))

should be like

=IFERROR(VLOOKUP(A125,sheet1!A1:D100,4,FALSE),VLOOKUP(A125,sheet1!A1:D100,2,FALSE))

 

[Hui]

I think you'd be better with this:

=IF(VLOOKUP(A125,Sheet1,4,FALSE)="",VLOOKUP(A125,Sheet1,2,FALSE),VLOOKUP(A125,Sheet1,4,FALSE))


Because VLOOKUP(A125,sheet1,4,FALSE) will only fail if the Range Sheet1 is less than 4 columns wide or the value in A125 doesn't exist


If A125 doesnt exist then it wont work for Column 2 either


Also should Sheet1 include a Range like Sheet1!A1:A100 or is it a Named Range?

 

[ACK93]

Thanks Hui for your response.


You are a genius! This works perfectly. (It is a named range)