Vlookup?

[demon8991]

Hi Guys!

I have a problem which i think can be solved but im not quite sure how to execute it.

I have a table which has 12 commodities down the left and 14 currencies across the top equating in 168 cells of information with a price specific to each commodity in a given currency.

We have a set of checklists 250 in total that are currently manually entered.

In each checklist we have to enter the commodity and currency and then the price for that combination. Is there a way to have the price cell automated based upon what is entered within the commodity and currency cell using information from our Price Table?

Thanks

[JBeaucaire]

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing what I think you're looking to do.

INDEX/MATCH Examples

The IndexMatch-Simple.xls file has a two-dimensional table that is essentially exactly what you're trying to do. You can see it in operation on the Main sheet or on the Distances sheet.

[martindwilson]

use index/match
=INDEX(A1:O13,MATCH(A19,A1:A13,0),MATCH(A20,A1:O1,0))
where a2:a13 are your 12 commodities and b1:O1 your 14 currencies
and a19 is input commodity a20 input currency

[demon8991]

Thank you very much guys you have helped no end!

The formula works a treat!