Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to compare two columns in Excel and if match, then copy the cell next to it

I've tried =IF(ISNUMBER(ISMATCH(D2,G:G,0)),H:H,"") to match numbers with a key, here are my rules:

Column D (starting at D2) match with Column G (starts at D2 as well)

If match, then Copy the cell in Column H that is matched through Columns D and G to Columns E

Is there a way to do this with a formula?

Summary

Column E is empty at the start, I want to copy the content of Column H to this column, but only if the row has been matched with column D and G.

like image 607
GivenPie Avatar asked Aug 09 '13 11:08

GivenPie


People also ask

How do you compare two columns in Excel and pull matching data?

Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option. A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.

How do you compare two cells and return YES if they are matched in Excel?

The following formula can help you quickly compare two cells in a row and return Yes if they are matched in Excel. Please do as follows. 1. Select a blank cell, copy formula =IF(A2=B2,"Yes","") into the formula bar and then press the Enter key.

How do I compare and copy data in Excel?

Arrange multiple Excel windows side by side To view more than 2 Excel files at a time, open all the workbooks you want to compare, and click the View Side by Side button. The Compare Side by Side dialog box will appear, and you select the files to be displayed together with the active workbook.


1 Answers

It might be easier with vlookup. Try this:

=IFERROR(VLOOKUP(D2,G:H,2,0),"")

The IFERROR() is for no matches, so that it throws "" in such cases.

VLOOKUP's first parameter is the value to 'look for' in the reference table, which is column G and H.

VLOOKUP will thus look for D2 in column G and return the value in the column index 2 (column G has column index 1, H will have column index 2), meaning that the value from column H will be returned.

The last parameter is 0 (or equivalently FALSE) to mean an exact match. That's what you need as opposed to approximate match.

like image 111
Jerry Avatar answered Sep 20 '22 01:09

Jerry