How to use match with multiple criteria in excel

0
votes

I have two sheets. I am matching sheet1 A2 value with sheet2 A2 to get Sheet1 C2 Value in sheet2 C2.

  • In Sheet1, if the number (in Column A) is repeating then it will have (blank) and AW. If the number is not repeating then it will have only (blank).

  • In Sheet2, if the number (in Column A) is repeating then it will have (blank) and AW. If the number is not repeating then it will have (blank) or AW. Actually (blank) = BY

  • In sheet1 and sheet2, Repeating values are always adjacent to each other.

Conditions are:

In Sheet1, A2 not repeating & B2 = (blank) then Copy the value and paste to sheet2 in Column C. It could be either BY and AW in Sheet B2. So, paste in both B2 & B3.

  • For Eg: In sheet1 A4 &A5 are equal; B4 = (blank); B5 = AW. So take C4 value and paste in Sheet2 both C4 AND C5.(Or the cell where it gets matched)
  • For Eg: In sheet1 A16; B16 = (blank). But In sheet2; B16 = AW. So take C16 value and paste in Sheet2 C16.(Or the cells where it gets matched). If both the values are (blank), then no problem.

In Sheet1, A2 repeating & B2 = (blank), A2 = A3, B3 = AW then Copy the value and paste to sheet2 in Column C2 and C3.

  • For Eg: In sheet1 A4 &A5 are equal; B4 = (blank); B5 = AW. So take C4 & C5 value and paste in Sheet2 both C4 AND C5.(Or the cells where it gets matched).

Help me. How to change my formula

=IFERROR(INDEX(Sheet1!$C$2:$C$4000; MATCH(1; (Sheet1!$A$2:$A$3000=Sheet2!$A2)*(Sheet1!$B$2:$B$3000=Sheet2!$B2);0));"")

=IFERROR(INDEX(Sheet1!$C$2:$C$4000;MATCH(Sheet2!A2;Sheet1!$A$6:$A$3000;0));"")

enter image description here

1
excelexcel-formulaexcel-2010pivot-table
you talk about pasting B16 value. in your question. I believe this is a typo and should be C16. and you say B16 is equal to BY, but your screen shot shows AW – Forward Ed
@ForwardEd Yes your are right, now i changed it. – Deepak
why is C14 and C15 on sheet 2 not equal to 22? seems to have the same conditions as rows 4 and 5 – Forward Ed
@ForwardEd Yes you are right. I did not fully typed the output. Just did some samples – Deepak
what is c17 supposed to be? – Forward Ed

1 Answers

0
votes

place the following in C2 and copy down:

=if(OR(B2="BY",SHEET1!C2<>""),SHEET1!C2,SHEET1!C1)

you will then want to go back and add a special case for your first row of data since you do not want to copy information from C1.

Alternatively you could add that as an option to your formula, but it MAY be a wasted bit of effort in all remaining rows.

EDIT:

In the case where the pairs of numbers are out of sequence you could use:

=IFERROR(IF(OR(B2="BY",INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0)+COUNTIF(A$2:A2,A2)-1)<>""),INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0)+COUNTIF(A$2:A2,A2)-1),INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0)+COUNTIF(A$2:A2,A2)-2)),"")