If 2 value match nee 3rd answer from another sheet

New Contributor

Dear Team,


I have two sheets

Against same if in Sheet1

A Columns and B columns match then from sheet2 columns results need in sheet1 cells area.



(In A word, in B nos, in word, in D word)

3 Replies


If I understand you correctly, in C2 on Sheet1:


=IFERROR(INDEX('Sheet2'!$D$2:$D$1000, MATCH(1, ('Sheet2'!$A$2:$A$1000=$A2)*('Sheet2'!$B$2:$B$1000=$B2), 0)), "")


Replace Sheet2 with the actual name of that sheet, and increase the number 1000 if you have more than 1000 rows of data.

Then fill or copy down.

It's not working.

Refer the reference attached sheet.


In your first post, you mentioned that you wanted the matching value from Sheet2 column D in Sheet1 column C. But Sheet2 column D is "Sr No". The column heading of Sheet1 column C is "Batch No". If you want the Batch No from Sheet2, you want the matching value from Sheet2 column C, not column


=IFERROR(INDEX(Sheet2!$C$2:$C$1000, MATCH(1, (Sheet2!$A$2:$A$1000=$A2)*(Sheet2!$B$2:$B$1000=$B2), 0)), "")


If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter. See the attached version.