Lookup Functions

[Pam]

I am trying to use the Lookup Functions (HLookup and
VLookup) to place the correct rate in a cell. This rate
is dependent upon the Customer and the From and To
Locations. It doesn't matter which is the From and which
is the To location....the rate is the same.

My problem is that if the rate spreadsheet that I have
set up does not list Both the possibilities ( ex. Tampa
to Miami, Miami to Tampa) then I get a "#N/A" error and
my formula :

=IF(VLOOKUP(CONCATENATE
($H9,$I9),'C:\ATNT\ADMIN\[rates.xls]Sheet1'!
$C$3:$W$500,10,FALSE)>0,VLOOKUP(CONCATENATE
($H9,$I9),'C:\ATNT\ADMIN\[rates.xls]Sheet1'!
$C$3:$W$500,10,FALSE),VLOOKUP(CONCATENATE
($I9,$H9),'C:\ATNT\ADMIN\[rates.xls]Sheet1'!
$C$3:$W$500,10,FALSE))

doesn't work.

Can you help?

 

[Frank Kabel]

Hi Pam
try the following (not tested)
=IF(ISERROR(VLOOKUP($H9 &
$I9,'C:\ATNT\ADMIN\[rates.xls]Sheet1'!$C$3:$W$500,10,FALSE)),VLOOKUP($I
9 &
$H9,'C:\ATNT\ADMIN\[rates.xls]Sheet1'!$C$3:$W$500,10,FALSE),VLOOKUP($H9
& $I9,'C:\ATNT\ADMIN\[rates.xls]Sheet1'!$C$3:$W$500,10,FALSE))

Though this will return an error if both location types are not found.

Frank

 

[Pam]

Frank,
Thank you....it works!
Pam

-----Original Message-----
Hi Pam
try the following (not tested)
=IF(ISERROR(VLOOKUP($H9 &
$I9,'C:\ATNT\ADMIN\[rates.xls]Sheet1'! $C$3:$W$500,10,FALSE)),VLOOKUP($I
9 &
$H9,'C:\ATNT\ADMIN\[rates.xls]Sheet1'! $C$3:$W$500,10,FALSE),VLOOKUP($H9
& $I9,'C:\ATNT\ADMIN\[rates.xls]Sheet1'! $C$3:$W$500,10,FALSE))

Though this will return an error if both location types are not found.

Frank

I am trying to use the Lookup Functions (HLookup and
VLookup) to place the correct rate in a cell. This rate
is dependent upon the Customer and the From and To
Locations. It doesn't matter which is the From and which
is the To location....the rate is the same.

My problem is that if the rate spreadsheet that I have
set up does not list Both the possibilities ( ex. Tampa
to Miami, Miami to Tampa) then I get a "#N/A" error and
my formula :

=IF(VLOOKUP(CONCATENATE
($H9,$I9),'C:\ATNT\ADMIN\[rates.xls]Sheet1'!
$C$3:$W$500,10,FALSE)>0,VLOOKUP(CONCATENATE
($H9,$I9),'C:\ATNT\ADMIN\[rates.xls]Sheet1'!
$C$3:$W$500,10,FALSE),VLOOKUP(CONCATENATE
($I9,$H9),'C:\ATNT\ADMIN\[rates.xls]Sheet1'!
$C$3:$W$500,10,FALSE))

doesn't work.

Can you help?

.