Cant Seem to Nail this Lookup

[Javier Diaz]

Good Morning everyone,

This one has stumped me. Of course, it may look like a piece of cake for
many of you, but when I have hundreds of formulas running around in one
workbook, my brain is starting to get fried. I even forgot to do a vlookup
for a few seconds this morning, lol.

Ok, so this is what I want to do.

I want a formula to look at an UPC in a range, then Look to see when that
upc is to launch in a range of cells, then return the column header of where
the indicator resides that it will launch. I'll show below;

UPC is in Sheet 1 in CEll A1

UPC data is in Sheet 2 in Column A

Colunms B1:E1 have dates like so;

9/15 6/8 9/18 etc.

Range B2:E500 has indicates like a 1 which will then indicate that if UPC
123 launches on 9/18, which would be cell D23, then return 9/18 which is the
header of that cell.

I hope I explained it right.

 

[Max]

One way ..

UPC source data assumed in Sheet2 as posted, with indicators being the
number: 1

In Sheet1,
Assume the UPC#'s would be listed in A1 down

Put in B1:
=INDEX(Sheet2!$1:$1,,MATCH(1,OFFSET(Sheet2!$1:$1,MATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.

 

[Javier Diaz]

Can this match if the # is greater than 0 instead of 1?

 

[Javier Diaz]

Something is weird, I changed the formula around and it doesnt seem to work

=INDEX('[Test'!$O$22:$U$22,,MATCH(1,OFFSET('[tEST'!$O$22:$U$22,MATCH(G20,'[tEST'!$G$23:$G$6500,0)-1,),0))

i NEED IT TO LOOK IN RANGE o23-U6500 FOR A QTY GREATER THAN 1, i DIDN mean
to specify it to 1 only.

Then if it finds something in whatever column between o and u in that range,
to return the corresponding column header, wghich is O22:U22. You were in
the right track, but maybe I'm lost.

 

[Toppers]

try:

=INDEX(Sheet1!A1:E1,MIN(IF(Sheet1!A2:A500=A1,IF(Sheet1!B2:E500>0,COLUMN(Sheet1!B1:E1)))))

Something is weird, I changed the formula around and it doesnt seem to work

=INDEX('[Test'!$O$22:$U$22,,MATCH(1,OFFSET('[tEST'!$O$22:$U$22,MATCH(G20,'[tEST'!$G$23:$G$6500,0)-1,),0))

i NEED IT TO LOOK IN RANGE o23-U6500 FOR A QTY GREATER THAN 1, i DIDN mean
to specify it to 1 only.

Then if it finds something in whatever column between o and u in that range,
to return the corresponding column header, wghich is O22:U22. You were in
the right track, but maybe I'm lost.

One way ..

UPC source data assumed in Sheet2 as posted, with indicators being the
number: 1

In Sheet1,
Assume the UPC#'s would be listed in A1 down

Put in B1:
=INDEX(Sheet2!$1:$1,,MATCH(1,OFFSET(Sheet2!$1:$1,MATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.

 

[Toppers]

.... enter as "array formula" with Ctrl+Shift+Enter

try:

=INDEX(Sheet1!A1:E1,MIN(IF(Sheet1!A2:A500=A1,IF(Sheet1!B2:E500>0,COLUMN(Sheet1!B1:E1)))))

Something is weird, I changed the formula around and it doesnt seem to work

=INDEX('[Test'!$O$22:$U$22,,MATCH(1,OFFSET('[tEST'!$O$22:$U$22,MATCH(G20,'[tEST'!$G$23:$G$6500,0)-1,),0))

i NEED IT TO LOOK IN RANGE o23-U6500 FOR A QTY GREATER THAN 1, i DIDN mean
to specify it to 1 only.

Then if it finds something in whatever column between o and u in that range,
to return the corresponding column header, wghich is O22:U22. You were in
the right track, but maybe I'm lost.

One way ..

UPC source data assumed in Sheet2 as posted, with indicators being the
number: 1

In Sheet1,
Assume the UPC#'s would be listed in A1 down

Put in B1:
=INDEX(Sheet2!$1:$1,,MATCH(1,OFFSET(Sheet2!$1:$1,MATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.

:
Good Morning everyone,

This one has stumped me. Of course, it may look like a piece of cake for
many of you, but when I have hundreds of formulas running around in one
workbook, my brain is starting to get fried. I even forgot to do a vlookup
for a few seconds this morning, lol.

Ok, so this is what I want to do.

I want a formula to look at an UPC in a range, then Look to see when that
upc is to launch in a range of cells, then return the column header of where
the indicator resides that it will launch. I'll show below;

UPC is in Sheet 1 in CEll A1

UPC data is in Sheet 2 in Column A

Colunms B1:E1 have dates like so;

9/15 6/8 9/18 etc.

Range B2:E500 has indicates like a 1 which will then indicate that if UPC
123 launches on 9/18, which would be cell D23, then return 9/18 which is the
header of that cell.

I hope I explained it right.

 

[Javier Diaz]

Ok, that formula seems to be doing the trick, but I'm getting a #Ref.

try:

=INDEX(Sheet1!A1:E1,MIN(IF(Sheet1!A2:A500=A1,IF(Sheet1!B2:E500>0,COLUMN(Sheet1!B1:E1)))))

Something is weird, I changed the formula around and it doesnt seem to work

=INDEX('[Test'!$O$22:$U$22,,MATCH(1,OFFSET('[tEST'!$O$22:$U$22,MATCH(G20,'[tEST'!$G$23:$G$6500,0)-1,),0))

i NEED IT TO LOOK IN RANGE o23-U6500 FOR A QTY GREATER THAN 1, i DIDN mean
to specify it to 1 only.

Then if it finds something in whatever column between o and u in that range,
to return the corresponding column header, wghich is O22:U22. You were in
the right track, but maybe I'm lost.

One way ..

UPC source data assumed in Sheet2 as posted, with indicators being the
number: 1

In Sheet1,
Assume the UPC#'s would be listed in A1 down

Put in B1:
=INDEX(Sheet2!$1:$1,,MATCH(1,OFFSET(Sheet2!$1:$1,MATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.

:
Good Morning everyone,

This one has stumped me. Of course, it may look like a piece of cake for
many of you, but when I have hundreds of formulas running around in one
workbook, my brain is starting to get fried. I even forgot to do a vlookup
for a few seconds this morning, lol.

Ok, so this is what I want to do.

I want a formula to look at an UPC in a range, then Look to see when that
upc is to launch in a range of cells, then return the column header of where
the indicator resides that it will launch. I'll show below;

UPC is in Sheet 1 in CEll A1

UPC data is in Sheet 2 in Column A

Colunms B1:E1 have dates like so;

9/15 6/8 9/18 etc.

Range B2:E500 has indicates like a 1 which will then indicate that if UPC
123 launches on 9/18, which would be cell D23, then return 9/18 which is the
header of that cell.

I hope I explained it right.

 

[Max]

Here's a revised formula which will cater for a single indicator (it can be
any type of input) per UPC# line in source range. The rest of the line range
must be left blank.

An illustrative sample, tailored to fit your set up is available at:
http://www.flypicture.com/download/MTIzNTA=
Match within range n return col header.xls

Array-entered* in B1
=INDEX(Sheet2!$O$22:$U$22,,MATCH(TRUE,OFFSET(Sheet2!$O$22:$U$22,MATCH(A1,Sheet2!$G$22:$G$6500,0)-1,)<>"",0))
*press CTRL+SHIFT+ENTER to confirm the formula

Copy B1 down

 

[Javier Diaz]

Ok Max, that forumla is just sick. Watch out David Blane, Max is coming.
Wow, how in the heck did you do that. Man, I thought I had some slight idea

Here's a revised formula which will cater for a single indicator (it can be
any type of input) per UPC# line in source range. The rest of the line range
must be left blank.

An illustrative sample, tailored to fit your set up is available at:
http://www.flypicture.com/download/MTIzNTA=
Match within range n return col header.xls

Array-entered* in B1:
=INDEX(Sheet2!$O$22:$U$22,,MATCH(TRUE,OFFSET(Sheet2!$O$22:$U$22,MATCH(A1,Sheet2!$G$22:$G$6500,0)-1,)<>"",0))
*press CTRL+SHIFT+ENTER to confirm the formula

Copy B1 down

Something is weird, I changed the formula around and it doesnt seem to work

=INDEX('[Test'!$O$22:$U$22,,MATCH(1,OFFSET('[tEST'!$O$22:$U$22,MATCH(G20,'[tEST'!$G$23:$G$6500,0)-1,),0))

i NEED IT TO LOOK IN RANGE o23-U6500 FOR A QTY GREATER THAN 1, i DIDN mean
to specify it to 1 only.

Then if it finds something in whatever column between o and u in that range,
to return the corresponding column header, wghich is O22:U22. You were in
the right track, but maybe I'm lost.

 

[Max]

welcome. glad that option worked out ok for you.