Vlookup embedded in mid formula

[StuartWhi]

Hi Experts,

I thought I had this but not able to get it or find a good reference.

I have the following,
Column D4 and below with currency details i.e. AUD (D4), CAD (D5) etc.
Column Z4:Z100 (CurrImport – range) I’m importing data from Google finance but its return format in a cell is “1 SGD = 0.9545 AUD” (the number of numbers varies per currency).

So my formula,
=MID(VLOOKUP(D4,CurrImport,1,FALSE),9,LEN(VLOOKUP(D4,CurrImport,1,FALSE))-9-3)
But this give me an #N/A.
What I’m trying to do is extract the numbers only out of the relative currency cell within the CurrImport range.

I look forward to any ideas,

Thanks,

Stuart.

 

[RonB1111]

Your vlookup() is trying to find a cell in Z4:Z100 that is "AUD", so it will never find this and always return #N/A.
You'll need to pull out both the currency type and amount in Z and put them in separate cells. Suggest using Data-TextToColumns to split Z which will put the amounts in AC and the type in AD. Then use this formula:

=INDEX(AC$4:AC$100,MATCH(D4,AD$4:AD$100,0))

Alternatively you could use =right(z4,3) to separate the curr type and =MID(Z4,9,LEN(Z4)-9-3) for the amount and then use then index/match (or vlookup if you place the type in a column to the left of amount).

 

[StuartWhi]

Hi Ron,

Thanks for the update, unfortunately Data-TextToColumns won't work for me because each time the data is re-imported from the website this would be required to be manually re-completed.
I have completed this via creating a mid and right to extract the relative areas then a vlookup as you suggested, was hoping there was a way to extract it directly but guess it's not possible.

Regards,

Stuart.

 

[RonB1111]

Try:

=MID(VLOOKUP("*"&D4,CurrImport,1,FALSE),9,LEN(VLOOKUP("*"&D4,CurrImport,1,FALSE))-9-3)

 

[StuartWhi]

Hi Ron,

Thanks that works great and allows me to simplify things thus the ideal solution.

I do find it very interesting that even through D4 the "lookup_value" only has the currency i.e. AUD that it's required to place a wildcard before the D4, so the wild card affects not only the lookup but also the ability to find D4 within the "table_array" section of the vlookup.

Great to know,

Stuart

 

[RonB1111]

You're welcome!

The vlookup() has a "False" in it, meaning to only find an exact match. Thus the need for the wildcard. If you used "True" in this case for something close if there's not an exact match, it would not necessarily deliver the result you expect.