IFS function to display the value of another cell

[urz2006]

Sorry this is probably a simple fix, but I can't make any combination work and can't see why this shouldn't work

I have a variety of currencies that may be used in a spreadsheet of costa, and want to have cells A4:B6 showing the current exchange rates

Rows 9 downwards will contatin various prices in column F, but will be in a variety of currencies. I will need some further calcuations to add in later....

I wan't cell H9 to look at what currency (ZAR, USD or EUR) that I type into E9 and then to look up what the corresponding exchange rate value is (i.e. the corresponding figure in B4/5/6), BUT I can't seem to make my function work, constantly get #NAME errors no matter what I try.

Below is latest example, I have tried various articles and tried putting TRUE at the end of the formula etc etc. Obviously doing something wrong but I can't make any combo work! Can any of you lovely people poitn me in right direction please?

(I was trying to make it do a more complex calculation initially, i.e if E9 = ZAR then H9 to take cell value of F9 and divide by cell value B4, if e9 = USD then H9 = F9 / B5, If E9 = EUR then H9 = F9 / E6 .....but I couldn't make that work, so I was trying to simplify it first and then use this calculated value in the next steps, but I can't make the more basic one work even)

Hope that makes some sense! thank you for any help

Ian

 

[arthurbr]

Your profile does not state what your XL version is. the NAME error probably indicates you have are using a version older than XL2019 ?
In your case you can use

=VLOOKUP($E$9,$A$4:$B$6,2,0)

to retrieve the rate
or

=$F$9/VLOOKUP($E$9,$A$4:$B$6,2,0)

to have it all in one go

 

[Fluff]

What version of Excel are you using?
The #NAME error suggests that you do not have the IFS function
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rpaulson]

in cell H9 try this =F9/VLOOKUP(E9,$A$4:$B$6,2,0)
then fill down

fyi, in think the last comma in your original formula should not be there.

 

[urz2006]

Thank you both so much, I knew there would be something straightforward, but I didn;t know this VLOOKUP function. works perfectly - thank you! And also good to know about the #name error thing. Brilliant - thanks all

 

[Fluff]

Can you please update your profile to show which version of Excel you are using, as requested?

 

[urz2006]

Done, thank you