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!!

 

[Aladin Akyurek]

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

Let A1:F8 house the sample you posted and O1 B96-723.

P1, control+shift+enter, not just enter:

=IF(COUNTIF(B2:F8,O1),INDEX(A2:A8,MIN(IF(B2:F8=O1,ROW(A2:A8)))),"")

 

[T. Valko]

Let A1:F8 house the sample you posted and O1 B96-723.

P1, control+shift+enter, not just enter:

=IF(COUNTIF(B2:F8,O1),INDEX(A2:A8,MIN(IF(B2:F8=O1,ROW(A2:A8)))),"")

I'm pretty sure that will return an incorrect result.

If you try it like this it'll work:

=IF(COUNTIF(B2:F8,O1),INDEX(A:A,MIN(IF(B2:F8=O1,ROW(A2:A8)))),"")

However, that suggestion was already made a couple of hours ago.

 

[Aladin Akyurek]

Let A1:F8 house the sample you posted and O1 B96-723.

P1, control+shift+enter, not just enter:

=IF(COUNTIF(B2:F8,O1),INDEX(A2:A8,MIN(IF(B2:F8=O1,ROW(A2:A8)))),"")

=IF(COUNTIF(B2:F8,O1),INDEX(A2:A8,MIN(IF(B2:F8=O1,ROW(A2:A8)-ROW(A2)+1))),"")

Although my trademark, I forgat to anchor the formula for robustness.

 

[T. Valko]

I forgat to anchor the formula for robustness.

It's a good thing someone is here to catch all those mistakes.

Thanks for the feedback!

 

[lbeemer]

nope, it is all text. It seems to have gotten worse too....I am still playing with that formula, but now can only get results using a couple of rows at a time.

This is weird for me, never seen anything like it.....

 

[T. Valko]

nope, it is all text. It seems to have gotten worse too....I am still playing with that formula, but now can only get results using a couple of rows at a time.

This is weird for me, never seen anything like it.....

Can you provide a small sample file that exhibits this behavior?

You can use a free file hosting site if need be.

 

[lbeemer]

Let A1:F8 house the sample you posted and O1 B96-723.

P1, control+shift+enter, not just enter:

=IF(COUNTIF(B2:F8,O1),INDEX(A2:A8,MIN(IF(B2:F8=O1,ROW(A2:A8)))),"")

Awesome!! I had to make a few small adjustments but this one worked like a charm!!! You have no idea how much time and headache you just saved me!

Thank you guys so much for the help. This forum is great!

 

[Aladin Akyurek]

Awesome!! I had to make a few small adjustments but this one worked like a charm!!! You have no idea how much time and headache you just saved me!

Thank you guys so much for the help. This forum is great!

You are welcome. Thanks for providing feedback.

Please replace the ROW(A2:A8) bit with ROW(A2:A8)-ROW(A2)+1, which makes the formula robust against row insertions:

=IF(COUNTIF(B2:F8,O1),INDEX(A2:A8,MIN(IF(B2:F8=O1,ROW(A2:A8)-ROW(A2)+1))),"")

 

[T. Valko]

Awesome!! I had to make a few small adjustments but this one worked like a charm!!! You have no idea how much time and headache you just saved me!

Thank you guys so much for the help. This forum is great!

Hmmm...

Now that doesn't make any sense!

But, if it works for you then that's all that matters.

Good luck and thanks for feeding back!

 

[Walkintall]

You are welcome. Thanks for providing feedback.

Please replace the ROW(A2:A8) bit with ROW(A2:A8)-ROW(A2)+1, which makes the formula robust against row insertions:

=IF(COUNTIF(B2:F8,O1),INDEX(A2:A8,MIN(IF(B2:F8=O1,ROW(A2:A8)-ROW(A2)+1))),"")

Hi I am completely new to this forum but I have been using this formula. I have run into a problem.




Ok so I did a bit of investigating and learned how to break down formulas and how they return values. Anyway I have found where the problem is. I hope that if you know where the problem is coming from then maybe you could figure it out as I have no idea how to do it. So here is the formula:

=IF(SUMPRODUCT(1*(INDIRECT.EXT("'C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded'!$E$4:$J$17")
=A15))>0,INDEX(INDIRECT.EXT("'C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded'!$K$4:$K$17")
,MIN(IF(INDIRECT.EXT("'C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded'!$E$4:$J$17")
=A15,[COLOR=#ff0000]ROW(INDIRECT.EXT("'C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded'!$K$4:$K$17"))
[/COLOR]-ROW(INDIRECT.EXT("'C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded'!$K$4"))
+1))),"")

The Red above is returning #VALUE when everything else is good. Can anyone help me out. I added this is as there may be Row insertion. I am also aware that I am using SUMPRODUCT and not COUNTIF but I am reffering to closed workbooks or at least that is the hope.

Thanks for your time!

Cheers.