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]

Thanks Aladin.

You are welcome.

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)))[/FONT]

I apologise if I'm asking too much!

i. The IF bit says: Return row numbers of the E:J cells which are have values equal to the the value of M6. This consists of 1 or more row nombers (rows, say).

ii. MIN picks out the smallest row obtained in (i).

iii. INDEX applies the smallest row obtained in (ii) to the range the function is looking at and returns the contents of the cell at that row.

The first formula execute the foregoing steps if only if the value of M6 occurs 1 or more times in the range COUNTIF is looking at. Otherwise a blank (i.e., "") is returned.

 

[marshke]

Thanks again Aladin, the only part that I think confuses me is the ROW part, why the -ROW and then +1.

Also, I notice the IF formula doesn't have a false value, is this normal?

 

[Aladin Akyurek]

Thanks again Aladin, the only part that I think confuses me is the ROW part, why the -ROW and then +1.

C4:C16 counts 13 cells or rows.

With

ROW(C4:C16)

we get...

{4;5;6;7;8;9;10;11;12;13;14;15;16}

the native row numbers.

Because we want to feed INDEX with cell numbers, we substract ROW(C4) and add 1 in order to obtain cell numbering from 1 on:

ROW(C4:C16)-ROW(C4)+1

==>

{4;5;6;7;8;9;10;11;12;13;14;15;16}-{4}+1

==>

{0;1;2;3;4;5;6;7;8;9;10;11;12}+1

==>

{1;2;3;4;5;6;7;8;9;10;11;12;13}

Also, I notice the IF formula doesn't have a false value, is this normal?

It has:

IF(COUNTIF(...),
     INDEX(),
     "")

When the COUNTIF result is 0, which means FALSE, a blank is returned.
When the COUNTIF result is not 0, which means >0, i.e. TRUE, the INDEX part is run to completion.

 

[marshke]

It has:

IF(COUNTIF(...),
     INDEX(),
     "")

When the COUNTIF result is 0, which means FALSE, a blank is returned.
When the COUNTIF result is not 0, which means >0, i.e. TRUE, the INDEX part is run to completion.

Sorry my apologies, I meant the IF within the shorter formula, when the IF is in the middle.

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

 

[Aladin Akyurek]

Sorry my apologies, I meant the IF within the shorter formula, when the IF is in the middle.

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

That delivers either an appropraite cell number (discussed earlier) or FALSE. SMALL ignores FALSE values as it is only in (cell) numbers.