Lookup Functions

  • Thread starter Thread starter Pam
  • Start date Start date
P

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?
 
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
 
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?
Click to expand...


.
Click to expand...
 
Back
Top