Lookup Functions

[Guest]

Hello,

I am trying to use a lookup function to calculate the following:

To determine the amount of foreign currency due a custorm
My Excel looks like this:

Columns A & B
Country traveling to: England
US Dollars to exchange: $1,000.00
Country's currency: Pound
Amount due customer:

Columns D, E, & F

Country Currency Exchange Rate
England Pound 0.6200
France Euro 0.9525
Germany Euro 0.9526
Canada Dollar 1.5603
How do I set up the calculation to calculate the foreign currency the
customer is due and that this figure appears in cell B8 labeled "Amount due
customer". The first one shoulw list $620, but my lookup function gives me
#N/A...please hlep

 

[Biff]

Hi!

What does your formula look like?

Try this:

=B3*IF(ISNUMBER(VLOOKUP(B2,D2:F5,3,0)),VLOOKUP
(B2,D2:F5,3,0),0)

Biff

 

[Guest]

My formula is like this:
Vlookup function:
=VLOOKUP(B$,$F$5:$F$17*$B$6,1,FALSE)

 

[Biff]

Nope, that ain't going to work!

Try this:

=B6*IF(ISNUMBER(VLOOKUP(B5,D5:F17,3,0)),VLOOKUP
(B5,D5:F17,3,0),0)

Biff

 

[Guest]

Biff,
I tried a new function HLookup-here it is:

=HLOOKUP(B6*F5,$F$5:$F$17*1000,$F$5:$F$17,0) and the formula on B8 is my
answer of 620 but after I select "Enter" the cell shows N/A

 

[Biff]

Hi!

Try my formula, it will work!

Simply adjust the cell references to match you layout.

Biff

 

[Myrna Larson]

That formula doesn't look right to me. Did you check the arguments in Help?

Unless this is an array formula, $F$5:$F$17*1000 isn't legal. And the 3rd
argument is supposed to be a row number, like 1, 2, 3, etc.

If the problem is that the numbers in the table have been divided by 1000 when
compared with your lookup value, I'd do it like this:

=HLOOKUP(B5*F5/1000,$F$5:$F$17,<some number between 1 and 13 here>,0)