Using IF and Vlook up

September 26, 2012 at 00:42:00
Specs: Windows 7
I have to find a mobile number is there in any of the 2 sheeets of data, I am trying to use a comibination of If and Vlook up, it works with the first sheet, even though the numbers are there in the second sheet, it still says #NA, the formula i used is =IF(VLOOKUP(F3,'Staff 11 May'!$C$2:$C$189,1,FALSE),F3,VLOOKUP(F3,Consultants!$B$128:$B$240,1,FALSE)),
Staff 11 and Consultants are 2 tabs in my work book
can you please assist

See More: Using IF and Vlook up

Report •

September 26, 2012 at 06:51:08
Excel formulas are evaluated from left to right. If any part of the formula returns an error, the evaluation stops and the error is returned in the cell. If the value in F3 is not found in 'Staff 11 May'!$C$2:$C$189, your first VLOOUP returns #N/A, so that's what you'll see.

The way around that is to check for the #N/A with the ISNA function.

You can do it 2 different ways:

1 - Check the first VLOOKUP and if it does not return a #N/A error, return F3, if it does, then do the second VLOOKUP:

=IF(NOT(ISNA(VLOOKUP(F3,'Staff 11 May'!$C$2:$C$189,1,FALSE))),F3,VLOOKUP(F3,Consultants!$B$128:$B$240,1,FALSE))

2 - (slightly shorter) Check the first VLOOKUP and if it does return a #N/A error, then do the second VLOOKUP, if not return F3.

=IF(ISNA(VLOOKUP(F3,'Staff 11 May'!$C$2:$C$189,1,FALSE)),VLOOKUP(F3,Consultants!$B$128:$B$240,1,FALSE),F3)

Note that in both cases, if F3 does not appear in either list, you will get a #N/A error.

BTW...To save keystrokes, the range_lookup argument (FALSE) be replaced with a 0:

=IF(ISNA(VLOOKUP(F3,'Staff 11 May'!$C$2:$C$189,1,0)),VLOOKUP(F3,Consultants!$B$128:$B$240,1,0,F3)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question