 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
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?
have you tried using VLOOKUP?
Sorry, misread. What Stoner said.
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.
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.
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.
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.