Find a value across multiple columns

[lbeemer]

Hi there,

I am trying to find a way to make this easy and have had no luck. Please help -

I have column A with "master" part numbers. In the columns next to the master part number, I have numerous "sub" part numbers that fall under the master part number. All the way to column HV in some instances.

I need to take a list of all the part numbers and have it search the range A1:HV13375 to find the corresponding part number. Once that is done I would like it to return the value in column A for that same row.

If I could find some way to use the match function (but across multiple rows) and return the row number, then I could use INDEX to return the column A value. I just can't figure out how to do step one.

I did have an equation (something) like this working =
{=IF(COUNTIF('image dupes'!$A$1:$HV$1000,O1),INDEX('image dupes'!$A$1:$A$1000,MAX(IF('image dupes'!$A$1:$HV$1000=O1,ROW('image dupes'!$A$1:$HV$1000)-ROW('image dupes'!$B$1)+1))),"")}

but I have somehow messed it up. Plus it only seemed to work on a smaller range and I am trying to look at some 2 million cells (but nowhere near all of them with values in them).

Any help would be greatly appreciated!!

 

[lbeemer]

(but across multiple rows) - should read (but across multiple columns)

 

[Matty]

Welcome to the Board...

This certainly sounds do-able.

Could you post a small example for us to have a play with? Along with the expected result?

Easiest way to post data is to ensure there are borders around the Cells, select those Cells, hit copy and the paste into your message.

Matty

 

[lbeemer]

For value B96-723 (found in cell O1) - I would like the formula in cell P1 to return B96-720 (the value from the first column in the table below on the row where B96-723 is located)

Hope that helps!

<table border="0" cellpadding="0" cellspacing="0" width="523"><col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:2925; width:60pt" span="5" width="80"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;width:92pt" height="17" width="123">Master Image</td> <td class="xl67" style="border-left:none;width:60pt" width="80">Sub Image</td> <td class="xl67" style="border-left:none;width:60pt" width="80">Sub Image</td> <td class="xl67" style="border-left:none;width:60pt" width="80">Sub Image</td> <td class="xl67" style="border-left:none;width:60pt" width="80">Sub Image</td> <td class="xl67" style="border-left:none;width:60pt" width="80">Sub Image</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">B96-650</td> <td class="xl67" style="border-top:none;border-left:none">B96-653</td> <td class="xl67" style="border-top:none;border-left:none">B96-656</td> <td class="xl67" style="border-top:none;border-left:none">B96-651</td> <td class="xl67" style="border-top:none;border-left:none">B96-654</td> <td class="xl67" style="border-top:none;border-left:none">B96-657</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">B96-694</td> <td class="xl67" style="border-top:none;border-left:none">B96-695</td> <td class="xl67" style="border-top:none;border-left:none">B96-696</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">B96-697</td> <td class="xl67" style="border-top:none;border-left:none">B96-698</td> <td class="xl67" style="border-top:none;border-left:none">B96-699</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">B96-720</td> <td class="xl67" style="border-top:none;border-left:none">B96-721</td> <td class="xl67" style="border-top:none;border-left:none">B96-722</td> <td class="xl67" style="border-top:none;border-left:none">B96-723</td> <td class="xl67" style="border-top:none;border-left:none">B96-724</td> <td class="xl67" style="border-top:none;border-left:none">B96-725</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">B96-790</td> <td class="xl67" style="border-top:none;border-left:none">B96-791</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">B96-792</td> <td class="xl67" style="border-top:none;border-left:none">B96-793</td> <td class="xl67" style="border-top:none;border-left:none">B96-794</td> <td class="xl67" style="border-top:none;border-left:none">B96-795</td> <td class="xl67" style="border-top:none;border-left:none">B96-796</td> <td class="xl67" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">B96-800</td> <td class="xl67" style="border-top:none;border-left:none">B96-799</td> <td class="xl67" style="border-top:none;border-left:none">B96-798</td> <td class="xl67" style="border-top:none;border-left:none">B96-797</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>

 

[mikerickson]

perhaps somehthing like

=INDEX(L:L, SUMPRODUCT((M1:HV1000="B96-723")*ROW(M1:HV1000)), 1)

 

[T. Valko]

For value B96-723 (found in cell O1) - I would like the formula in cell P1 to return B96-720 (the value from the first column in the table below on the row where B96-723 is located)

Hope that helps!

<TABLE cellSpacing=0 cellPadding=0 width=523 border=0><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" span=5 width=80><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="WIDTH: 92pt; HEIGHT: 12.75pt" width=123 height=17>Master Image</TD><TD class=xl67 style="BORDER-LEFT: medium none; WIDTH: 60pt" width=80>Sub Image</TD><TD class=xl67 style="BORDER-LEFT: medium none; WIDTH: 60pt" width=80>Sub Image</TD><TD class=xl67 style="BORDER-LEFT: medium none; WIDTH: 60pt" width=80>Sub Image</TD><TD class=xl67 style="BORDER-LEFT: medium none; WIDTH: 60pt" width=80>Sub Image</TD><TD class=xl67 style="BORDER-LEFT: medium none; WIDTH: 60pt" width=80>Sub Image</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>B96-650</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-653</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-656</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-651</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-654</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-657</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>B96-694</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-695</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-696</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>B96-697</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-698</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-699</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>B96-720</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-721</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-722</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-723</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-724</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-725</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>B96-790</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-791</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>B96-792</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-793</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-794</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-795</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-796</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>B96-800</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-799</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-798</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">B96-797</TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD><TD class=xl67 style="BORDER-TOP: medium none; BORDER-LEFT: medium none"></TD></TR></TBODY></TABLE>

You were pretty close with your formula.

Try this...

Array entered**:

=IF(COUNTIF('image dupes'!$B$1:$HV$1000,O1),INDEX('image dupes'!$A:$A,MAX(IF('image dupes'!$B$1:$HV$1000=O1,ROW('image dupes'!$B$1:$HV$1000)))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[lbeemer]

I could not get mikerickson's to work

T. Valko - I feel like yours is wanting to work, but something else might be up....I am getting the result #NAME? - which is closer than I got, but it is only showing up for the parts that do have matches in the table, other parts show nothing (which is fine)
<table style="width: 254px; height: 18px;" border="0" cellpadding="0" cellspacing="0"><col width="254"><tr height="17"> <td class="xl66" style="height:12.75pt; width:191pt" align="center" height="17" width="254">
</td> </tr></table>

 

[T. Valko]

I could not get mikerickson's to work

T. Valko - I feel like yours is wanting to work, but something else might be up....I am getting the result #NAME? - which is closer than I got, but it is only showing up for the parts that do have matches in the table, other parts show nothing (which is fine)
<TABLE style="WIDTH: 254px; HEIGHT: 18px" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL width=254><TBODY><TR height=17><TD class=xl66 style="WIDTH: 191pt; HEIGHT: 12.75pt" align=middle width=254 height=17>

</TD></TR></TBODY></TABLE>

Make sure all the words in the formula are spelled correctly.

 

[lbeemer]

I checked that - still no luck.

When I bump my range down (way down) to like 20 rows it will all the sudden work fine. I guess it is an issue with using such a large range. Since I need the columns more than the rows, I will just try to search by my larger items first and hopefully can expand my range as I go along.

This is still much easier than going one-by-one.

Thanks so much guys!!!

 

[T. Valko]

I checked that - still no luck.

When I bump my range down (way down) to like 20 rows it will all the sudden work fine. I guess it is an issue with using such a large range. Since I need the columns more than the rows, I will just try to search by my larger items first and hopefully can expand my range as I go along.

This is still much easier than going one-by-one.

Thanks so much guys!!!

Are there any formulas within the referenced range that return the #NAME? error?