Matrix Lookup

Hi,

I've been trying to get my head around how to do this for a while now, I know I need to use a combination of match and vlookup etc. but I'm not sure how.

Please Login or Register  to view this content.

I need to convert prices in a but vary the colum reference based on the value

e.g. A product costs 1.15 AUD, I would then like to be able to automatically convert it to 0.79 EUR i.e. lookup of the value with the column number dependant on the value in AUD. So if I were to have 2.25 NZD it would return 1.19 EUR.

Let me know if you need further explanation,
Thanks

I was working on something almost identical to ramanhanhrm except I made the value of a currency a drop-down subject to the Country drop-down option.

Probably not added much more to what is here already but here it is:

CURRENCY CONVERTER.xlsx

What's interesting M1234 is that I refer to these 3 currencies almost daily!!

This works perfectly in the file ramananhrm uploaded but when I put it into my file it just comes up with #N/A?

SASD.png

Here's the error after I click evaluation

M1234 are you aware you will need to Name your columns and/or rows on your spreadsheet, (select the range and give it a name in the 'Name Box'), plus the INDIRECT formula in the Data Validation will need to be changed to match the cell you are referring to if using a second Drop down selection as in my spreadsheet.

Yes I have changed the cell references to make them applicable to my own sheet as you can see here:

http://s14.postimg.org/dbgav68hd/SASD.png

Originally Posted by rajeshturaha

See the name range in the file ramananhrm uploaded. You have to define the range.

You can try my formula without defining name range

I have created named ranges now but it still returns the same error. Sorry for this confusion, I can't figure out why it isn't working!