If value match on different sheet, then copy data from the sheet one to other sheets

Question

I have Sheet1, Sheet2, Sheet3, Sheet4

I want the formula i.e. if I enter any value in Sheet2, Sheet3, Sheet4 in Column E, then it should match that value with Sheet1 Column A, In case of value match, the data value of sheet1 should be copied to all other sheets.

EG:- In sheet1 I have the below mentioned data.

(A Column    /         B Column    /    C Column)
(Product Value   /      Cost       /    Packing)
(200          /          100       /       50)
(150          /          70        /       20)
(300          /          120       /       50)
(500          /          300       /       100)

To illustrate, if in Sheet2, Column E if I put a value 150, then the formula should scan for that value in Sheet1, Column A , and find its match in 2nd row, then it should copy the data of column B (i.e 70) to Sheet2 column N and similarly copy the Sheet1 column C (i.e 20) to Sheet2 column O.

If I enter value to Sheet2, Sheet3, Sheet4, Sheet5 then formula should scan the value only from Sheet1 and copy the data,to its respective places as described in the preceding paragraph.

Answer 1

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.