CODEWITHSUNDEEP
excelexcel-formula
thedev
thedev

Reputation: 2906

Excel formula using 2 sheets

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

Upvotes: 0

Views: 6993

Answers (2)

GnomeDePlume
GnomeDePlume

Reputation: 1642

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

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

My approach would be along these lines:

  1. 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...

  2. 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

  3. 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

Upvotes: 1

Brian
Brian

Reputation: 2229

If the worksheets are tabs in the same workbook you can reference them by name =SUM(Sheet1!A1+Sheet2!A1)

if they are coming from different workbooks then you have to reference the path and name in the formula

=SUM(Sheet1!A1+'C:[Book2.xlsx]Sheet1'!A1)

Upvotes: 0

Related Questions