CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulagoogle-sheets-formula
Ally
Ally

Reputation: 35

if 2 cells match, return the value on another spreadsheet

Here's an example table:

'Sheet one'

Column A: Name of Fruits

Column B: Quantity Ordered

Column C: Price

'Sheet two'

Column A: Name of Fruits

Column B: Quantity Ordered

How do I return the price value that matches from Sheet1 Column C to Sheet2 Column C (new column)?

I am following this formula but it doesn't provide me the correct matching price:

=INDEX('Sheet one'!B:B, MATCH('Sheet two'!C2, A:A,0))

Upvotes: 1

Views: 3295

Answers (2)

teylyn
teylyn

Reputation: 35915

Your lookup key consists of two columns. You have two options:

  1. create a helper column where you concatenate the two column values, then use that for the lookup

  2. dynamically combine the lookup value and lookup columns. This will be slow if you use whole columns, so keep it to only the rows with data.

    =index(Sheet1!C1:C10,match(Sheet2!A1&Sheet2!B1,index(Sheet1!A1:A10&Sheet1!B1:B10,0),0))

Upvotes: 1

N.O.Davis
N.O.Davis

Reputation: 513

If I'm reading your formula correctly, it looks like your index is returning Column B, which is quantity ordered.

=INDEX(*column to return*)MATCH(*value to check*, *column to search for value*, *search type*))

You can see 6 different possibilities HERE (and below) depending on what you're trying to do. Copy the sheet to make edits.

  1. Standard Index/Match:=INDEX(Sheet1!$C$2:$C,MATCH(A3,Sheet1!$A$2:$A,FALSE))
  2. Google Sheets Query (1 Column Return/1 Match Key): =QUERY(Sheet1!$A$2:$D,"Select C where A='"&D3&"'",0)
  3. Index/Match/Multiply:=INDEX(Sheet1!$C$2:$C,MATCH(A3,Sheet1!$A$2:$A,FALSE))*H3
  4. Google Sheets Query (2 Column Return/1 Match Key): =QUERY(Sheet1!$A$2:$D,"Select B, C where A='"&K3&"'",0)
  5. Index/Match with Multiple Match Keys: =ARRAYFORMULA(INDEX(Sheet1!$C$2:$C,MATCH(1,(O3=Sheet1!$A$2:$A)*(P3=Sheet1!$B$2:$B),0))) See HERE for use with Excel.
  6. Google Sheets Query (1 Column Return/2 Match Keys): =QUERY(Sheet1!$A$2:$D,"Select C where A='"&S3&"' and B like '"&T3&"' ",0)

If my answer or teylyn's worked, please check the green check mark to accept the answer.

Upvotes: 0

Related Questions