Lookup in multiple columns

[sapai]

I have a lookup problem. I have to lookup for matching data in cells A2 to
E50, match it against a static value "BOOST" and if i hit a match, return the
corresponding value in column "F". Multiple cells in A2 to E50 range may have
the value "BOOST". It is K if I catch the first match an give corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

 

[RagDyeR]

This *array* formula will return the match with the highest row number:

=INDEX(F2:F50,MAX((A2:E50="boost")*ROW(1:49)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have a lookup problem. I have to lookup for matching data in cells A2 to
E50, match it against a static value "BOOST" and if i hit a match, return
the
corresponding value in column "F". Multiple cells in A2 to E50 range may
have
the value "BOOST". It is K if I catch the first match an give corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

 

[sapai]

Thanks you. This works. Thanks for info on CSE..
I need to extend the formula for 1000's of rows. I could fix row/column
values with "$" sign. But how can i do it for ROW(1:49) ?

 

[John C]

....ROW($1:$49)...
--
John C

Thanks you. This works. Thanks for info on CSE..
I need to extend the formula for 1000's of rows. I could fix row/column
values with "$" sign. But how can i do it for ROW(1:49) ?

 

[sapai]

Thanks John.. i should have guessed it.

i have hit one more problem with this formula. If I change the lookup <in
this example "boost"> as dynamic variable for each row, and if one of this
value donot exist in A2:E50, it is returning me by default first value in
cell F2.

Is there a way to force blank in this cell with the same formula?

 

[sapai]

i have hit one more problem with this formula. If I change the lookup <in
this example "boost"> as dynamic variable for each row, and if one of this
value donot exist in A2:E50, it is returning me by default first value in
cell F2.

 

[RagDyer]

Try this instead ... still an *array* formula, needing CSE:

Assume variable is in Column G, starting in Row1,
Copy down as needed.

=IF(COUNTIF(A$2:E$50,G1)=0,"",INDEX(F$2:F$50,MAX((A$2:E$50=G1)*(A$2:E$50>0)*ROW($1:$49))))

 

[RagDyer]

Posted wrong formula.
Would still work but ... has superfluous argument:

Use this *array* formula instead, and copy down as needed after CSE:

=IF(COUNTIF(A$2:E$50,G1)=0,"",INDEX(F$2:F$50,MAX((A$2:E$50=G1)*ROW($1:$49))))