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]

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.

In what follows, # is substituted for

C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\[Cage 1.xlsm]Cage Inventory Coded

Not having a 64bit version of the morefunc.xll add-in, the following is not tested:

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

Control+shift+enter, not just enter:

=IF(SUM(IF(INDIRECT.EXT("'#'!E4:J17")=A15,1)),
  INDEX(INDIRECT.EXT("'#'!K4:K17"),
   MIN(IF(INDIRECT.EXT("'#'!E4:J17")=A15,
    ROW(INDIRECT.EXT("'#'!K4:K17"))-ROW(INDIRECT.EXT("'#'!K4"))+1))),
  "")

Note 1. The formula seems to be correct. Control+shift+enter might be the clue.
Note 2. INDIRECT and INDIRECT.EXT lock the ranges they refer to, so dollarizing is not needed.

 

[marshke]

Could someone please assist with my situation?

I tried from a previous suggestion and changing it to my cell ranges etc to o avail.

I tried to use - =IF(COUNTIF(E6:J14,M6),INDEX(C6:C14,MIN(IF(E6:J14=M6,ROW(C6:C14)))),"")

Then pressing ctrl+shift+enter


[TABLE="width: 577"]
<tbody>[TR]
[TD="width: 39, bgcolor: transparent, align: right"]4[/TD]
[TD="width: 51, bgcolor: transparent"][/TD]
[TD="width: 146, bgcolor: transparent, colspan: 2"]C[/TD]
[TD="width: 51, bgcolor: transparent"]E[/TD]
[TD="width: 51, bgcolor: transparent"]F[/TD]
[TD="width: 51, bgcolor: transparent"]G[/TD]
[TD="width: 51, bgcolor: transparent"]H[/TD]
[TD="width: 51, bgcolor: transparent"]I[/TD]
[TD="width: 51, bgcolor: transparent"]J[/TD]
[TD="width: 51, bgcolor: transparent"]K[/TD]
[TD="width: 77, bgcolor: transparent"]L[/TD]
[TD="width: 47, bgcolor: transparent"]M[/TD]
[TD="width: 56, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 6"]Your week numbers[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Today[/TD]
[TD="bgcolor: transparent, colspan: 2"]Week Number[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Kev[/TD]
[TD="bgcolor: transparent"][/TD]
[TD]4[/TD]
[TD]13[/TD]
[TD]26[/TD]
[TD]35[/TD]
[TD]45[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]05/01/2015[/TD]
[TD="bgcolor: #E6B8B7, colspan: 2"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Tom[/TD]
[TD="bgcolor: transparent"][/TD]
[TD]5[/TD]
[TD]16[/TD]
[TD]27[/TD]
[TD]37[/TD]
[TD]46[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Paul[/TD]
[TD="bgcolor: transparent"][/TD]
[TD]6[/TD]
[TD]17[/TD]
[TD]28[/TD]
[TD]38[/TD]
[TD]47[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Nikki[/TD]
[TD="bgcolor: transparent"][/TD]
[TD]7[/TD]
[TD]18[/TD]
[TD]29[/TD]
[TD]39[/TD]
[TD]48[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]This week[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Marie[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]49[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Answer L10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Julie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD]9[/TD]
[TD]21[/TD]
[TD]31[/TD]
[TD]41[/TD]
[TD]50[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]James[/TD]
[TD="bgcolor: transparent"][/TD]
[TD]10[/TD]
[TD]23[/TD]
[TD]32[/TD]
[TD]42[/TD]
[TD]51[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Katie[/TD]
[TD]2[/TD]
[TD]11[/TD]
[TD]24[/TD]
[TD]33[/TD]
[TD]43[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Shane[/TD]
[TD="bgcolor: transparent"][/TD]
[TD]12[/TD]
[TD]25[/TD]
[TD]34[/TD]
[TD]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

@marshke

Would you describe the problem you have in words instead of a formula which seemingly does not work?

 

[marshke]

@Aladin Akyurek

Basically E6 - J14 are all unique numbers (week numbers).

I have week number 2 currently in M6 which I want a formula to tell me who this week belongs to.

So whatever week number shows in M6, the formula needs to then display the name from column C

I hope this explains my problem well enough.

 

[Aladin Akyurek]

@Aladin Akyurek

Basically E6 - J14 are all unique numbers (week numbers).

I have week number 2 currently in M6 which I want a formula to tell me who this week belongs to.

So whatever week number shows in M6, the formula needs to then display the name from column C

I hope this explains my problem well enough.

You could have added that the result should be Katie. Such really helps a would be helper. That said...

Control+shift+enter, not just enter:

=INDEX($C$6:$C$14,MIN(IF($E$6:$J$14=$M6,ROW($C$6:$C$14)-ROW($C$6)+1)))

If you want to apply some control...

=IF(COUNTIF($E$6:$J$14,$M6),INDEX($C$6:$C$14,
  MIN(IF($E$6:$J$14=$M6,ROW($C$6:$C$14)-ROW($C$6)+1))),"")

 

[marshke]

Thank you Aladin.

I apologise or not adding this information. I'm finding posting messages a pain to type, it's as if the page is refreshing constantly and causing letters to not be typed.

Thanks again for the information above, it has worked wonders!

 

[Aladin Akyurek]

Thank you Aladin.

I apologise or not adding this information. I'm finding posting messages a pain to type, it's as if the page is refreshing constantly and causing letters to not be typed.

Thanks again for the information above, it has worked wonders!

You are welcome.

 

[marshke]

A quick question on Excel in general, I have done intermediate excel twice as a course for work, but wonder if you have any tips on where I can learn Excel from home as I know how powerful this tool is, just don't know as much as I wish I did. Work won't send me on any advanced training courses

 

[Aladin Akyurek]

A quick question on Excel in general, I have done intermediate excel twice as a course for work, but wonder if you have any tips on where I can learn Excel from home as I know how powerful this tool is, just don't know as much as I wish I did. Work won't send me on any advanced training courses

Some people seems to benefit from (tutorial) podcasts and videos. Visiting forums like this one would help if you study questions and implement and study the answers provided.

 

[marshke]

Thanks Aladin.

Would someone mind explaining/breaking down that formula?

[FONT=lucida console][SIZE=2]=IF(COUNTIF($E$6:$J$14,$M6),INDEX($C$6:$C$14,
MIN(IF($E$6:$J$14=$M6,ROW($C$6:$C$14)-ROW($C$6)+1))),"")[/SIZE][/FONT]

[FONT=lucida console]=INDEX($C$6:$C$14,MIN(IF($E$6:$J$14=$M6,ROW($C$6:$C$14)-ROW($C$6)+1)))

I apologise if I'm asking too much!
[/FONT]