IF and double VLOOKUP

S

SamuelT

Hi all,

I have the following formula:

=IF(VLOOKUP(A2,HUB.xls!interserve,2,FALSE),(VLOOKUP(A2,HUB.xls!interserve,24,FALSE)-14),"")

What I'm trying to do is run a VLOOKUP in the file HUB.xls first looks
for a specific ID number. If this ID number is there, I then want Excel
to look in the 24th column of HUB.xls (the range being 'interserve') and
subtract 14 from it. I thought that the above formula would do it, but
no. Just what I need on aFriday afternoon!!!

Can anyone suggest where I'm going wrong?

TIA,

SamuelT
 
A

Alex

First, does this work in the actual workbook? HUB.xls!interserve does
not look correct to me, but I try not to do much outside the workbook I
am working in while doing lookups.

Second, try to use ISERROR. If the value is not in the list, then an
error is returned by vlookup. If there is an error, then ISERROR
returns true. Here is an example (the external links may not be right,
though):

=IF(iserror(VLOOKUP(A2,HUB.xls!interserve,2,FALSE)),"",(VLOOKUP(A2,HUB.xls!interserve,2­4,FALSE)-14))

Hope this helps.

Alex.
 
G

Guest

Hi
Your formula looks Ok to me. What result are you getting? What do you want
to return if the number is not there?
One point is that your first VLOOKUP checks column 2 and yet you want to
return column 24. Is that right?

Andy.
 
P

Pete_UK

Try this:

=IF(ISNA(VLOOKUP(A2,[HUB.xls]interserve,1,FALSE),"",
VLOOKUP(A2,[HUB.xls]interserve,24,FALSE)-14)

You might have to include the sheet name before the named range.

Hope this helps.

Pete
 
S

SamuelT

Thanks guys. Turns out I've got Friday afternoon brain-fry. :eek: Ther
was a much simpler way of trying to achieve my goal with just a singl
VLOOKUP. I'm at a new job - trying to show off probably!

I'll keep your suggestions for another time though...no doubt I'll nee
them.

Cheers,

Samuel
 
Top