Matching when values are in several possible columns

**mgreg** · 06-10-2012, 08:39 AM

Hi Everyone,

I'm having trouble coming up with a code for matching one name to several columns, one of which will contain the matching name. To explain better, I have a spreadsheet with birds that have indentifying rings. One ring is metal, the other plastic. The plastic was the easiest to read so that was how birds were recorded. Unfortunately the plastic ring would become difficult to read over time and have to be replaced. Thus, one bird could be recorded under several different plastic ring codes. I now want to match the bird recorded as one of it's possible plastic ring codes to its metal code - and include additional information in adjacent columns. I can do this easily for matching one column (eg:Plastic 1) to the list of observations (shown in spreadsheet, J to L), it's including all possible names (Plastic 2, Plastic 3) where I'm having trouble.

So in summary, I need to change the formuals in columns J, K, and L to include columns G and H in addition to F. Can anyone suggest a fix? (the name in column J isn't important, just it's placement in the correct row).

Thanks!
G

**dilipandey** · 06-10-2012, 09:14 AM

Hi mgreg,

Welcome to the forum.

I have added row 18 into the data and formula showed the results in column J,K,L.. what else do you need?

Match example.xlsx
Regards,
DILIPandey

<click on below 'star' if this helps>

**mgreg** · 06-11-2012, 09:15 AM

Hi Dilipandey,

Thank-you very much for taking the time to help, it's very much appreciated! Unfortunately I didn't explain myself very well. I need the formulas in columns J,K, and L to be able to match up to ring codes in columns G and H in addition to F. So for example, both YB:AJJ and GW:MO were observed on March 12. At the time of observation YB:AJJ was 6-48757's only plastic code so it is listed in column F, but GW:MO was 6-48765's second ring code so it is listed in column G. So the formula would have to be able to match to each name regardless of which column (F, G, or H) it is listed in. Do you think there is a solution?

Cheers,
G

**JosephP** · 06-11-2012, 10:11 AM

will there ever be matches in more than one column for the same row? if there won't then you can use
=SUMPRODUCT(($A$3:$A$17=F3:H3)*ROW($A$3:$A$17))
to get the matching row number (or 0 for no match) and then use that in INDEX formulas to pull the data you want.

**mgreg** · 06-12-2012, 12:43 PM

Hi JP, There won't ever be matches in more than one column for the same row. Your formula works great but I'm lost at the next step. I can't get excel to recognize that the value given in your formula corresponds to a row number

**JosephP** · 06-12-2012, 03:10 PM

say that formula is in O3, in L3
=INDEX(A:A,$O3)
and copy across.

**mgreg** · 06-13-2012, 11:10 AM

Hi JP, this works! Because the second formula (column K) still gives a bird if the first formula (column J) gives a zero instead of a row number (i.e., the bird was not actually observed), i added a numbered column (I). I then sorted Column J, deleted all records with 0, then sorted Column I to put everything back right. It gives exactly what i want. Thanks!!