Comparing two columns and if there is a match...

Hi,

I am trying to comparing two 'text' columns and I would like that if a match occurs the value in an adjacent column, same row of the match will be displayed.

The match function is a bit unclear to me. so for example below the formula should look for Amir in colum D and give back the figure "4" or eventually a string of text in column E

example.jpg


Thanks for your help

Carmine

Hi Carmine,

I saw my name in this problem so I needed to do it. I think you are looking for the Index and Match functions put together. See the attached for an example of what I mean.

Hi Carmine,

Looking at the picture in your post #1 you have a second option for getting an answer. If what you are looking for is in the leftmost column of your table of data you can use a VLookup function also. VLookup is a special case of the Index/Match combination.

In B1 you could use this formula:

=VLookup(A1,$D$1:$E$6,2,False)

This says to look at A1 and find it in the list of words in Column D. When it finds it go to the Second column over and return that value. The False part means it must be an exact match to what is in A1.

We need to use the Index/Match combination when what we want to return is to the left of what we are looking for. This idea makes TABLES of data very important in Excel. It can work with Rows and Columns that are together and lined up much more effectively than numbers spread out on a worksheet. I hate to see people make their data "look pretty" with colors and merged cells without using tables. The structure of the data makes Excel work better if it is in Tables of data.