How to return the entire row based on two cells from two different Excel sheets matching?

Question

I have a large Excel data file, with data from two different sheets. I want to be able to match the partner_identifier values from SO Sheet 2 to the values in BB Sheet 1. For the rows in BB Sheet 1 that do match, I want to bring the entire row into a new sheet.

I need help coming up with a formula for this. I've tried VLOOKUP and IF variations, but I think I need a more complex formula. I can't do an =IF('SO Sheet 2'!D3='BB Sheet 1'!D3) because the matching values could be in different rows.

Right now, I have (and I know this is off because it returned "No" for every row, even the ones with a matching value) :

=IF(D3='SO Sheet 2'!D3:D16,'BB Sheet 1'!D3,"No")

Any insight would be greatly appreciated!

Answer 1

You can achieve the result in 2 steps. in Sheet "BB sheet 1", you need to add a column E with XLOOKUP formula to find the matching identifier in "SO sheet 2"

=XLOOKUP(D2,'SO Sheet 2'!$A$2:$A$14,'SO Sheet 2'!$A$2:$A$14,"No")

The above will list all the matching identifier and put "No" wherever if couldn't match the identifier.

Then all you got to do is , in a new sheet, enter the formula in cell A1

=FILTER('BB sheet 1'!A:D,'BB sheet 1'!E:E<>"No")

Answer 2

If you are using latest version of excel (Excel for Microsoft 365) - you can use XLOOKUP or FILTER formula.

Example:

= FILTER('SO Sheet 2'!A3:D16, 'SO Sheet 2'!D3:D16=D3)

Check here for details about these formulas: XLOOKUP, FILTER