Currency Conversion of multiple types to separate column

[mpr505]

Hi,

I have very limited Excel experience so am learning on the go.

I'm trying to automatically convert a variety of currencies to their USD and AUD equivalents.

I followed a tutorial on how to add currency conversion to a spreadsheet:
https://www.youtube.com/watch?v=phll6zxsUgU


This is the formula I am using to convert currency between two different types as part of the embedded exchange table:


=IFERROR(ROUND(IF(AB3="US Dollar",1,VLOOKUP(AB3,Info!Y$5:Z$59,2,FALSE))/IF(AB2="US Dollar",1,VLOOKUP(AB2,Info!Y$5:Z$59,2,FALSE)),2),"")

So far I have determined the following instructions that need to occur:


Column P contains the ISO country code for currencies (ie USD, AUD, GBP, PHP, EGP, SGD, AED, etc) as well as a numerical value (price of item). So would look like this: "AED 1249".


Column Q needs to have the USD conversion of the equivalent cell in column P.


If the cell in column P contains "USD", then it just needs the value of that cell to be copied to column Q.


If the cell in column P does NOT contain "USD", then the ISO code needs to be identified, the currency exchange equivalent located in the Info! worksheet, and the value associated with that cell divided by the USD exchange rate for that currency. For example:

1 USD= AED 3.672946
1 AED = USD 0.272261


AED 1249 / USD 3.67 = USD 340





This needs to be conducted over a range of 10000 rows in column P.

I have determined that I will need to use the ISNUMBER/SEARCH functions, and the following works for an individual cell:
=IF(ISNUMBER(SEARCH("*USD*",P6)),P6)

Here are some screenshots of what I have in terms of data:

Cell formatting - Imgur: The most awesome images on the Internet
Currency conversion formula - Imgur: The most awesome images on the Internet
Currency list - Imgur: The most awesome images on the Internet

Is there a way I can do this that will be the one formula for the entire range of cells?

Thanks!