Lookup Functions

S

Sharon

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
 
B

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
 
B

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
 
S

Sharon

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
 
B

Biff

Hi!

Try my formula, it will work!

Simply adjust the cell references to match you layout.

Biff
 
M

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top