Excel formula using 2 sheets

Question

I have 2 excel sheets and I need a formula to get the value for column * from sheet 2 based on the values of the type and model columns.

Sheet1                  Sheet2              

        *???            |              modelA   modelB  modelC
typeA   100   modelA    |       typeA   100     1        1
typeB   50    modelB    |       typeB   1       50       1  
...                     |       ...         
typeY   20    modelA    |       typeY   20      1        1

Thanks in advance for any suggestions

GnomeDePlume GnomeDePlume · Accepted Answer · 2012-01-17T18:53:58

Let's try and break this up into smaller problems.

You have 'type' and 'model' information, and want to search in sheet 2 for a match
When you find matches, you want to use them to refer to the value in sheet 2
... and then you want to refer to that value to bring it into sheet 1.

My approach would be along these lines:

We can use the MATCH function to look up a piece of information, and then return it's position within a range. So =MATCH("typeA", A4:A6) should give us the answer '1'. If we use MATCH to look up the position of the type and model cells in sheet 2, then we can use these as coordinates to make an address...
Given the coordinates, for example '1,1', we want to make an address. The ADDRESS function will do this for us! For example, =ADDRESS(1,1) will give $A$1
We can then use the INDIRECT function to look at (and return) the contents of this address. In sheet 1, =INDIRECT("$A$1") would return 'Sheet1'.

Attempting to cobble that together, I came up with this:

=INDIRECT("sheet2!"&ADDRESS(MATCH(A1, Sheet2!$A$1:$A$5), MATCH(C1, Sheet2!$A$1:$D$1)))

It seems to work!

How does that look to you? Are there any parts that I need to explain better?

Regards, AZ

Excel formula using 2 sheets

2 Answers