If Statement requiring explanation

  • Thread starter Thread starter CP
  • Start date Start date
C

CP

Hi
I have received much help on this site but I end up copy and pasting the
answers to my questions, I was wondering if someone could explain (break
down) an answer I received earlier so I can actually understand whats
happening?

=IF(ISNA(VLOOKUP(B2,SHEET!A:B,2,0)),â€â€,VLOOKUP(B2,SHEET2!A:B,2,0))

Many thanks
 
If your vlookup function gives #na() error leave the cell blank else pop up
the output.

Isna function - checks the output Value giving #N/A (value not available)
error value.
 
If the vlookup fails to find a match, it generates an #N/A. So the
ISNA(....) returns TRUE in that case. That causes the IF to return a blank
("") instead of an error. If the vlookup finds a match, the ISNA returns
FALSE, so the IF returns the vlookup result. Net, this runs the vlookup and,
IF the result is #N/A, it changes the result to a blank.
 
run the vlookup formula by itself and see if you get an NA. I also suggest
that you NOT look in the entire column.
 
Sure.

The core formula is:

=VLOOKUP(B2,SHEET!A:B,2,0)

The problem is that if the value in B2 does not appear in column A, an error
message will appear. If IF statements says that if VLOOKUP() displays an
error, display a blank cell. If VLOOKUP() does not display an error, display
what VLOOKUP() returns.
 
Back
Top