How to paste value in cell based on the another cell in Excel

I have 2 sheets: 1 sheet contains numbers (some are duplicated) and another sheet contains the name of that number. I need to paste the name beside every number in the 1st sheet. Example shown below:

Sheet 2: The names below are to be added to every number:

12345 Hardware
12548 Software
32546 Network
85468 Development

Sheet 1: I need to paste the name beside every equal number. For example wherever I find the number 12345, I need paste Hardware name beside 12345 cell.

12345
12548
32546
85468
12345
12548
32546
85468
12345
12548
32546
85468
12345
12548
32546
12345
12548
32546
12345
12548
32546
12345
12548
32546
12345
12548
32546

2 Answers

This type of problem is exactly what the VLOOKUP function was created for.

I'll assume that your numbers are in column A of Sheet1 (beginning in row 1) and your numbers and names are in columns A and B of Sheet2. Enter the following formula in cell B1 of Sheet1 and copy it to the other cells in column B.

=VLOOKUP(A1,Sheet2!$A:$B,2,False)

The function works by looking up the first parameter (the number in A1) in the table in columns A and B in Sheet2 and returning the value from the 2nd column of the table. The 4th parameter (False) is only required if the table is not sorted by number.

What your looking for are called Nested IF statements (full tutorial here ).

Essentially, you need a formula in the A column that looks for values in the B column (columns and Rows are interchangeable in this example). In this example, think of A as the destination for the data and B as the sample used to determine the answer to your statement. Just make sure you're closing each statement with a ")" at the end (observe the sample statement houses two Nested statements and ends with 2 ")".

=IF(B1=12345, "Hardware", IF(B1=12548, "Other"))

You can continue this indefinitely and then use the plus at the lower right of the finished A1 Cell to automatically change as many rows down in the A column you wish to use. By using this method the formulas in each A column cell filled in this manner will be directed at the corresponding B cell. So essentially you only need to update the A1 cell then drag the plus sign down to fill the remaining A Cells (which will read "FALSE" until data is plugged into the B Column cells).

Excel Image