lookup easy one

lars

Board Regular
Joined
Mar 27, 2002
Messages
105
=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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
=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
 
Upvote 0
Jay, I tried it and it didn't work maybe more than 7 nested if's casued the problem.

Thanks
 
Upvote 0
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
 
Upvote 0
Use the isna formula on the cells 16-21. then the sum calc will read the NA's as 0'.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top