lookup easy one

lars · Apr 25th, 2002, 05:09 PM

=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

Jay Petrulis · Apr 25th, 2002, 05:15 PM

Try,

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

Mark W. · Apr 25th, 2002, 05:17 PM

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 ]

lars · Apr 25th, 2002, 05:20 PM

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

Qroozn · Apr 25th, 2002, 05:21 PM

=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 ]

lars · Apr 25th, 2002, 05:22 PM

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

Thanks

Mark W. · Apr 25th, 2002, 05:24 PM

Quote:

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 ]

lars · Apr 25th, 2002, 05:25 PM

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

Thanks

Qroozn · Apr 25th, 2002, 05:25 PM

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

Mark W. · Apr 25th, 2002, 05:26 PM

See my reply posting above.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 05:09 PM	#1
lars Board Regular Join Date: Mar 2002 Location: California Posts: 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

Apr 25th, 2002, 05:15 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Try, =IF(COUNTBLANK(E16:E21)=5,0,your mega formula)

Apr 25th, 2002, 05:17 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

Apr 25th, 2002, 05:20 PM	#4
lars Board Regular Join Date: Mar 2002 Location: California Posts: 105	just blanks in the column sometimes and numbers at other times.

Apr 25th, 2002, 05:21 PM	#5
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	=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 ]

Apr 25th, 2002, 05:22 PM	#6
lars Board Regular Join Date: Mar 2002 Location: California Posts: 105	Jay, I tried it and it didn't work maybe more than 7 nested if's casued the problem. Thanks

Apr 25th, 2002, 05:25 PM	#8
lars Board Regular Join Date: Mar 2002 Location: California Posts: 105	Qroonz, didn't work either, not sure what's happening Thanks

Apr 25th, 2002, 05:26 PM	#10
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	See my reply posting above.