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

 

[lars]

Mark, maybe i misunderstood you previous question but the left most column of the range "PAX" are all voyage numbers i.e. A112
,A113, D112 etc the lookup value range e15-e21 are the possible blank cells.

Hope that makes sense

 

[Mark W.]

On 2002-04-25 16:28, lars wrote:
Mark, maybe i misunderstood you previous question but the left most column of the range "PAX" are all voyage numbers i.e. A112
,A113, D112 etc the lookup value range e15-e21 are the possible blank cells.

Hope that makes sense

Great! So the value, 0, isn't a valid "voyage number". This allows you to insert a row into the 'PAX' range and setup a value that you'd like your VLOOKUP to return if E15:E21 are blank. You see... when E15:E21 are blank your VLOOKUP function will search for a 0 entry in 'PAX'... so put one there for it to find! Does this make sense? You're configuring your table for all possible lookup values.

 

[lars]

Mark, that works, but the range PAX is a download and I was hoping to copy the download in without having to manipulate or change it in anyway. But it works and that is great.

Thanks Mark
Lars

 

[lars]

Qroonz, I tried it and it works if I put in the second position but if I add other voayges in the range e16-e21 nothing adds up. it just returns the first voyage find

 

[Mark W.]

On 2002-04-25 16:43, lars wrote:
Mark, that works, but the range PAX is a download and I was hoping to copy the download in without having to manipulate or change it in anyway. But it works and that is great.

Thanks Mark
Lars

There are ways around that too. You could use the Data | Get External Data menu command to import your data and have PAX filled in just below a permanent 0 entry on your worksheet.

The important thing (for me at least) is that you're thinking out-of-the-box when you implement a solution such as this. Most users would try to "fix it" by constructing a convoluted formula.
This message was edited by Mark W. on 2002-04-25 16:51

 

[lars]

Mark , I take that back it doesn't work if you add other voyages to E16-E21 it only works with E15. hmmmmm....

 

[Mark W.]

On 2002-04-25 16:50, lars wrote:
Mark , I take that back it doesn't work if you add other voyages to E16-E21 it only works with E15. hmmmmm....

What value did you associate with 0 in PAX table?

 

[Qroozn]

did you change the cell references when you copied the formula?. or are you just repeating the same lookup throughtout all your cells?

 

[lars]

Mark, I just typed in a 0...no good?


Qrooze, the formula is in one cell only.

 

[lars]

I have to go to pick up my daughter

Thanks for the help I will check this out tomorrow morning. Maybe then I can repost and we can have some more fun with it.

Thanks again you guys