| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Statement requiring explanation
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement requiring explanation
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. "CP" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement requiring explanation
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. "CP" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement requiring explanation
run the vlookup formula by itself and see if you get an NA. I also suggest
that you NOT look in the entire column. -- Don Guillett Microsoft MVP Excel SalesAid Software "CP" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement requiring explanation
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. -- Gary''s Student - gsnu200832 "CP" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| XIRR Requiring Sale Price 0 | Excel Discussion (Misc queries) | |||
| Sum Problem requiring help | Excel Worksheet Functions | |||
| Requiring data in certain cells | Excel Worksheet Functions | |||
| requiring a field | Excel Worksheet Functions | |||
| Formula requiring two different criterias | Excel Worksheet Functions | |||
Hybrid Mode
