Field matching formula help needed.

Hi guys, great forum you have here.

I browsed through the forum looking to find a way to solve my problem, but could not find it. Maybe someone here can help me out.

I have lots of data, and it would be impractical to do it manually. Here is an example:

A B C D

Game ID Game Game ID Game

Mortal Kombat 1051 Mario
Halo 538 Sonic
Mario 697 Mortal Kombat
Street Fighter 472 Street Fighter
210 Halo
152 FIFA
5210 PGR


I have a long list like this. Once list has more games than the other one, but the game id's are the same. Therefore what I need is a formula that will look at B, then find it in D, if it does find the exact match in D to output the figure in C to A.

When the automation is finished it should look like this:

A B C D

Game ID Game Game ID Game

697 Mortal Kombat 1051 Mario
210 Halo 538 Sonic
1051 Mario 697 Mortal Kombat
472 Street Fighter 472 Street Fighter
210 Halo
152 FIFA
5210 PGR


I hope that all displays properly for you guys. Thank you for your time.

Kind regards.

Andy

In A2, try:

=IF(ISNUMBER(MATCH(B2,D:D,0)),INDEX(C:C,MATCH(B2,D:D,0)),"")

copied down.

You can select that column and go to Data|Text to Columns... then select Next.

Select Space and Treat Consecutive delimiters as one

Click Finish.

Then try this formula:

Please Login or Register  to view this content.

adjusting ranges to suit and then confirm it with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear, then copy it down.

did say only the column with numbers!

Enter in the formula and Change the ranges D1:D100, etc.. to match the size of your database, then make sure the cell is active (i.e. cursor is in the cell somewhere). Then hold the CTRL and SHIFT keys down and press ENTER. You will see { } brackets appear around the formula.

This is a special Array formula that requires that key combo to work.

Originally Posted by andysmith

Hi NBVC.

Sorry for the late rely. Thanks, I managed to make your original code work. It helped a lot.

I would like to do this in return "If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.".

However I am not sure where?

Thanks again :-)

On any one of my posts within this thread you will see a dark blue ribbon at the top.. on the right side are some icons... click the one that represents a balance (or scale)... then selelct I approve and click ok.