Lookup Functions

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

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

>-----Original Message-----
>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
>
>
>.
>

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

"Biff" wrote:

> 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
>
> >-----Original Message-----
> >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
> >
> >
> >.
> >
>

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

>-----Original Message-----
>My formula is like this:
>Vlookup function:
>=VLOOKUP(B$,$F$5:$F$17*$B$6,1,FALSE)
>
>"Biff" wrote:
>
>> 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
>>
>> >-----Original Message-----
>> >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,
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" wrote:

> 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
>
> >-----Original Message-----
> >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
> >
> >
> >.
> >
>

Hi!

Try my formula, it will work!

Simply adjust the cell references to match you layout.

Biff

>-----Original Message-----
>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" wrote:
>
>> 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
>>
>> >-----Original Message-----
>> >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
>> >
>> >
>> >.
>> >
>>
>.
>

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)



On Sat, 19 Feb 2005 20:39:02 -0800, Sharon <Sharon@discussions.microsoft.com>
wrote:

>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" wrote:
>
>> 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
>>
>> >-----Original Message-----
>> >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
>> >
>> >
>> >.
>> >
>>