lookup easy one

[lars]

=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

 

[Mark W.]

On 2002-04-25 16:56, lars wrote:
Mark, I just typed in a 0...no good?

So your left-most column of PAX looks something like...

0
A112
A113
D112

...Right? What did you mean by "it only works with E15"?

 

[lars]

Mark, my data looks like what you said I will put the 0 at the bottom but other than that it's the same. My range of cells E15-E21 are voyages and when I put the A228 for example in E15 The lookup will find A228 in the range PAX but when I put A229 in E16 then #n/a comes back.

 

[Mark W.]

On 2002-04-26 10:08, lars wrote:
Mark, my data looks like what you said I will put the 0 at the bottom but other than that it's the same. My range of cells E15-E21 are voyages and when I put the A228 for example in E15 The lookup will find A228 in the range PAX but when I put A229 in E16 then #n/a comes back.

Then "A229" must not be in PAX. Are you sure that no trailing spaces were inadvertantly added to either your lookup value or the table_array, PAX?

 

[lars]

I'm positive, I will play with it some more to see if I can come up with another solution. Any other thoughts?

Thanks

 

[Mark W.]

BTW, I have no idea what you're trying to accomplish with...

IF(ISNUMBER((E15)*1),(E15)*1,(E15))

...if 0 has been added to PAX you can simply use...

=VLOOKUP(E15,PAX,12,FALSE)

 

[lars]

I know that formula for some reason worked with this download and the regular lookup did not. Not sure why....maybe now with the 0 I will try the lookup as you stated and see what happens.

Thanks for the help Mark!!