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

Question

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.

Solution 1

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.

Solution 2

try this formula in column E:

=IF( AND( ISNUMBER(D2), D2=G2), H2, "")

your error is the number test, ISNUMBER( ISMATCH(D2,G:G,0) )

you do check if ismatch is-a-number, (i.e. isNumber("true") or isNumber("false"), which is not!.

I hope you understand my explanation.