IF and double VLOOKUP

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

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.

"SamuelT" <SamuelT.28f53m_1148653500.5335@excelforum-nospam.com> wrote in
message news:SamuelT.28f53m_1148653500.5335@excelforum-nospam.com...
>
> 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
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
> http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=545868
>

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):

=3DIF(iserror(VLOOKUP(A2,HUB.xls!interserve,2,FALSE)),"",(VLOOKUP(A2,HUB.xl=
s!interserve,2=AD4,FALSE)-14))

Hope this helps.

Alex.

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

Thanks guys. Turns out I've got Friday afternoon brain-fry. There was a much simpler way of trying to achieve my goal with just a single 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 need them.

Cheers,

SamuelT