Help with lookup function

[zepp]

I'm looking to use the lookup function to find a text value (e.g. "product 1") which happens to be in say, cell A1, then scan across Product 1's row and return the first value which is not 0, within the range b1:k1. In the real function I can't specify which row to look across becauseI don't know which row Product 1 will appear, which seems to be making it much harder.

I have very limited lookup knowledge so any help is appreciated.

Thanks

[JBeaucaire]

Like so... see attached:

[zepp]

Sorry, I mistyped.

I meant to type: "I'm looking to use the lookup function to find a text value (e.g. "product 1") which happens to be in say, cell A1, then scan across Product 1's row and return the first value which is not 0, within the range b1:k1."

So in your example sheet, it would look all the way down column A, find "cat" (out of any number of entries in column A, each of my sheets has a different number of entries), and then only look at the 10 number entries along the row of "cat" (even if there are more than 10) starting at b1 and ending at k1.

Is this possible to do?

[JBeaucaire]

Ok, to make this dynamic the search cell cannot be in the same column as the searched strings. So:

1)Move the search cell to B1.

2) Enter this array formula in C1:

=INDEX(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10), MIN(IF(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10)>0, COLUMN(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10)), ""))-1)

...confirm the formula by pressing CTRL-SHIFT-ENTER to activate the array.

That will limit the search to the columns B:K of the matched row.

[zepp]

Excellent, works great!

Thanks a lot!

[Rwilliams_09]

I know this may be an annoyance but I was trying to understand this type of formula the other day for a projection sheet at work. Could you break down what the formula is doing for me?

=INDEX(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10), MIN(IF(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10)>0, COLUMN(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10)), ""))-1)

[Teethless mama]

I know this may be an annoyance but I was trying to understand this type of formula the other day for a projection sheet at work. Could you break down what the formula is doing for me?

=INDEX(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10), MIN(IF(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10)>0, COLUMN(OFFSET(INDEX($A:$A, MATCH(B1,$A:$A, 0)), , 1, , 10)), ""))-1)

Don't worry that formula. It's a volatile and a mile long formula.

Try this compact/non volatile version:

=INDEX(B2:K99,MATCH(B1,A2:A99,),MATCH(TRUE,INDEX(B2:K99,MATCH(B1,A2:A99,),)<>0,))

Ctrl+Shift+Enter, not just Enter

[JBeaucaire]

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.