Viewing 7 posts - 1 through 7 (of 7 total)
  • Excel Formula Hep
  • geetee1972
    Free Member

    In Column A I have symbols designating currency types, e.g. EUR or USD
    In Column B I have amounts in those currencies

    On the same tab I have a separate table (on the same tab) that lists the exchange rates of these currencies against sterling, e.g. a list of five currency symbols in one column and next to them a list of the exchange rates.

    I want to create a formula that returns the sterling exchange rate next to the amount in column B by looking up that rate in the separate table.

    I’ve managed to get a Vector based LOOKUP function to work for some but not all of the currencies. I can’t work out why it doesn’t work for all of them.

    I have ordered the Lookup Vector from smallest to largest and vice versa and that doesn’t make a difference. My formula is as follows:

    LookUp Value = cell in Column A where the currency is designated
    Lookup Vector = all the cells where the currency symbols are located
    Lookup Result = the cells adjacent to the above where the exchange rates are located.

    I get the #NA error for some but not all of the iterations.

    Any ideas why?

    Stoner
    Free Member

    have you tried using VLOOKUP?

    RobHilton
    Free Member

    Sorry, misread. What Stoner said.

    tinribz
    Free Member

    If the V lookup is a bit puzzling try this in C1

    =IF(A1=”GBP”,$H$5,IF(A1=”USD”,$H$6,IF(A1=”EUR”,$H$7,IF(A1=”AAA”,$H$8,IF(A1=”BBB”,$H$9,0)))))

    Where H5 to H9 has your exchange rates in.

    Or the V lookup is =VLOOKUP($A1,$G$5:$H$9,2,FALSE)

    Where your symbols are in col G startig at row 5 and rates in H. Don’t forget the $ to stop the table moving when you drag the formula.

    apj
    Free Member

    If the $ doesn’t fix it, might be the use of symbols as there may be more than one character code that look identical but won’t match using vlookup. Try =cell(A1) for example to get the code of your first symbol, and compare it with what is in your “vector” (where’s that term come from? 😯 ).

    Or just use GBP USD EUR etc.

    geetee1972
    Free Member

    Thanks for taking the time to reply guys, greatly appreciated. In the end I reached the same conclusion to the problem as Tinribz’s method but I would still like to work out why the Lookup formula was working for some currencies but not others.

    I was using the $ symbol to fix the cells and using ‘USD’ rather than ‘$’. I’ll give the VLOOKUP a try.

    thegeneralist
    Free Member

    The two usual problems using vlookup are:
    Ensure the final parameter is set to do an exact match rather than approximate match ( I think False, but check)
    The other common gotcha is that you set everything up in the first cell and then copy n paste it down to the rest of the column. But as you’re doing so Excel changes the cell references in the formula and shifts them down by the same amount. The $ in your cell reference will fix this, as said above.

Viewing 7 posts - 1 through 7 (of 7 total)

The topic ‘Excel Formula Hep’ is closed to new replies.