Cant Seem to Nail this Lookup
 

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.

Cant Seem to Nail this Lookup
 

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,M ATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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

"Max" wrote:

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,M ATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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" wrote:

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,M ATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

try:

=INDEX(Sheet1!A1:E1,MIN(IF(Sheet1!A2:A500=A1,IF(Sh eet1!B2:E5000,COLUMN(Sheet1!B1:E1)))))

"Javier Diaz" wrote:

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" wrote:

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,M ATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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

"Toppers" wrote:

try:

=INDEX(Sheet1!A1:E1,MIN(IF(Sheet1!A2:A500=A1,IF(Sh eet1!B2:E5000,COLUMN(Sheet1!B1:E1)))))

"Javier Diaz" wrote:

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" wrote:

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,M ATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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

"Toppers" wrote:

try:

=INDEX(Sheet1!A1:E1,MIN(IF(Sheet1!A2:A500=A1,IF(Sh eet1!B2:E5000,COLUMN(Sheet1!B1:E1)))))

"Javier Diaz" wrote:

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" wrote:

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,M ATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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

"Toppers" wrote:

try:

=INDEX(Sheet1!A1:E1,MIN(IF(Sheet1!A2:A500=A1,IF(Sh eet1!B2:E5000,COLUMN(Sheet1!B1:E1)))))

"Javier Diaz" wrote:

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" wrote:

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,M ATCH(A1,Sheet2!A:A,0)-1,),0))
Copy down to return the required results, ie the col headers from Sheet2's
row1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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(Sheet 2!$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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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(Sheet 2!$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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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
on how to create advance formulas, but your a Genius, how how how how how. I
must study this one big time.

"Max" wrote:

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(Sheet 2!$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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

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
on how to create advance formulas, but your a Genius, how how how how how. I
must study this one big time.

"Max" wrote:

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(Sheet 2!$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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote:
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.

Cant Seem to Nail this Lookup
 

welcome. glad that option worked out ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote in message
...
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
on how to create advance formulas, but your a Genius, how how how how how.
I
must study this one big time.

Cant Seem to Nail this Lookup
 

welcome. glad that option worked out ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Javier Diaz" wrote in message
...
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
on how to create advance formulas, but your a Genius, how how how how how.
I
must study this one big time.