New Contributor

# If 2 value match nee 3rd answer from another sheet

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

# Re: If 2 value match nee 3rd answer from another sheet

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.

# Re: If 2 value match nee 3rd answer from another sheet

It's not working.

Refer the reference attached sheet.

# Re: If 2 value match nee 3rd answer from another 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.