vlookup and match first value greater than
 

I need to lookup a product id and return the date of the first value greater
than zero
For instance

a b c d e f g
1 Date 4/12 4/19 4/26 5/3 5/10 5/17
2 Product A 0 11 35 0 0 125
3 Product B 35 50 75 100 25 36

If I lookup Product A in the spreadsheet and want to get results from
columns e through g I would want to see 5/17 as the result.

I have this formula which works against a fixed row reference but have been
unable to incorporate a lookup value into this to return the same result.
INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g20,0),0))

How do I add to this formula so that I can type a Product ID into a cell and
lookup and return this data from the table? ie. lookup Product A and show the
first date greater than 0.

vlookup and match first value greater than
 

One way
Assuming input in say, A7 for the product, eg: Product A
put this in B7, normal ENTER to confirm will do:
=INDEX(OFFSET($E$1:$G$1,MATCH(A7,A2:A3,0),),MATCH( TRUE,INDEX(OFFSET($E$1:$G$1,MATCH(A7,A2:A3,0),)0, ),0))
Success? hit YES below
--
Max
Singapore
---
"MPI Planner" wrote:
I need to lookup a product id and return the date of the first value greater
than zero
For instance

a b c d e f g
1 Date 4/12 4/19 4/26 5/3 5/10 5/17
2 Product A 0 11 35 0 0 125
3 Product B 35 50 75 100 25 36

If I lookup Product A in the spreadsheet and want to get results from
columns e through g I would want to see 5/17 as the result.

I have this formula which works against a fixed row reference but have been
unable to incorporate a lookup value into this to return the same result.
INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g20,0),0))

How do I add to this formula so that I can type a Product ID into a cell and
lookup and return this data from the table? ie. lookup Product A and show the
first date greater than 0.

vlookup and match first value greater than
 

Hi,

I do not see the problem with your formula. Your formula yields the answer
as 5/17. The only correction you have to make is that the INDEX function
should be ,
INDEX($e2:$g20,0).

Also, why is the range E2:G2 - why is not B2:G2

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"MPI Planner" wrote in message
...
I need to lookup a product id and return the date of the first value
greater
than zero
For instance

a b c d e f g
1 Date 4/12 4/19 4/26 5/3 5/10 5/17
2 Product A 0 11 35 0 0 125
3 Product B 35 50 75 100 25 36

If I lookup Product A in the spreadsheet and want to get results from
columns e through g I would want to see 5/17 as the result.

I have this formula which works against a fixed row reference but have
been
unable to incorporate a lookup value into this to return the same result.
INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g20,0),0))

How do I add to this formula so that I can type a Product ID into a cell
and
lookup and return this data from the table? ie. lookup Product A and show
the
first date greater than 0.

vlookup and match first value greater than
 

Think the earlier can be simplified to just:
=INDEX($E$1:$G$1,MATCH(TRUE,INDEX(OFFSET($E$1:$G$1 ,MATCH(A7,$A$2:$A$3,0),)0,),0))
where A7 = input for the product, as before
--
Max
Singapore
---

vlookup and match first value greater than
 

Assuming your data in A1:G3
Criteria in A7

=MAX(INDEX((A2:A3=A7)*(E2:G30)*E2:G3,))

Normally enter


"MPI Planner" wrote:

I need to lookup a product id and return the date of the first value greater
than zero
For instance

a b c d e f g
1 Date 4/12 4/19 4/26 5/3 5/10 5/17
2 Product A 0 11 35 0 0 125
3 Product B 35 50 75 100 25 36

If I lookup Product A in the spreadsheet and want to get results from
columns e through g I would want to see 5/17 as the result.

I have this formula which works against a fixed row reference but have been
unable to incorporate a lookup value into this to return the same result.
INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g20,0),0))

How do I add to this formula so that I can type a Product ID into a cell and
lookup and return this data from the table? ie. lookup Product A and show the
first date greater than 0.