Excel – Compare Excel sheet column data, then fill data to column next if any match

Sounds like you could accomplish your goal with the VLookup function. Add this formula to Sheet1 F1: =IFERROR(VLookup(E1,Sheet2!A:D,4,FALSE),"")

That formula will copy the cell into sheet1 if a match is found, but if no match is found the cell will remain blank.

This is relatively simple and straight forward to do with vlookup.
I am making the assumption that the value to lookup is in Cell E2 (regardless of the sheet)

Use the following formulas for Column N2 on Sheet2,3,4,5, etc and then drag down to how many rows as needed:

=VLOOKUP(E2,Sheet1!$A$2:$C$5,2,FALSE)

Use the following formulas for Column O2 on Sheet2,3,4,5, etc and then drag down to how many rows as needed:

=VLOOKUP(E2,Sheet1!$A$2:$C$5,3,FALSE)

Adjust the "$A$2:$C$5" formula part to match the appropriate range in your Sheet1 table. Or replace the "5" with the number of rows with product data in Sheet1. You can also use "Sheet1!A:C" to just search all the rows.

This formula does not "copy" the data to the other sheets, it merely displays the value based on the lookup value in column E.

The first argument of VLOOKUP is the Lookup value (the "product value"). We want to reference the cell in column E for this. (I used Cell E2, as I assume you have a column header in E1).

The second argument of VLOOKUP is the Table array (Sheet1!$A$2:$C$5). This is where we want to look for our value from column E. Keep in mind, vlookup only searches thru the first column for the criteria value. The dollar signs ($) make sure that the lookup table reference stays static and does not change if you try to autofill the formula down all the rows in your column.

The third argument of VLOOKUP is the column index. When it finds a match in the first column, this integer will tell vlookup which column to return. 1 returns the 1st column, 2 returns the 2nd, and so on.

The "FALSE" parameter tells the formula to only find exact match. If this is set to "TRUE" and your list is not sorted in ascending order, you will run into trouble with vlookup trying to find the closest match rather than an exact match.