Lookup in multiple columns

S

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

R

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

S

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) ?

J

John C

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

sapai said:
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) ?

S

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?

S

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.

R

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))))

R

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))))