Thanks:  0
Likes:  0

# Thread: lookup easy one

1. =VLOOKUP(IF(ISNUMBER((E15)*1),(E15)*1,(E15)),pax,12,FALSE)+VLOOKUP(IF(ISNUMBER((E16)*1),(E16)*1,(E16)),pax,12,FALSE)

The formula takes cells E15-E21 just not shown here for simplicity.

What can I add to the formula so if cells 16-21 are blank it won't return a #N/A?

thanks

2. Try,

=IF(COUNTBLANK(E16:E21)=5,0,your mega formula)

3. Is zero in the domain of the left-most column of your table_array (pax)? If not, just add a row in your table_array for 0 and the value you'd rather have your VLOOKUP return.

[ This Message was edited by: Mark W. on 2002-04-25 16:20 ]

4. just blanks in the column sometimes and numbers at other times.

5. =isna(VLOOKUP(IF(ISNUMBER((E15)*1),(E15)*1,(E15)),pax,12,FALSE)+VLOOKUP(IF(ISNUMBER((E16)*1),(E16)*1,(E16)),pax,12,FALSE )),0

i think

[ This Message was edited by: Qroozn on 2002-04-25 16:22 ]

6. Jay, I tried it and it didn't work maybe more than 7 nested if's casued the problem.

Thanks

7. On 2002-04-25 16:20, lars wrote:
just blanks in the column sometimes and numbers at other times.
If 'pax' contained...

{1,"one";2,"two";3,"three"}

...and you'd rather not have =VLOOKUP(A1,pax,2,0) return #N/A when cell A1 is blank then change 'pax' to...

{0,"zero";1,"one";2,"two";3,"three"}

...and then =VLOOKUP(A1,pax,2,0) will return "zero" instead.

You "build" the "smarts" into the table rather than the formula. Get it?

[ This Message was edited by: Mark W. on 2002-04-25 16:25 ]

8. Qroonz, didn't work either, not sure what's happening

Thanks

9. Use the isna formula on the cells 16-21. then the sum calc will read the NA's as 0'.

10. See my reply posting above.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•