Please help with a formula

[Accesshelp]

Hello all,

I need help with a formula that is similar to vlookup or hlookup.

In my Excel file, I have various values in cells from G2 to M2 and in cells
from G1 to M1. I am looking for a formula that if any of the values in G2 to
M2 is "A", give the value from the cell above. For example, if I2 has a
value of "A", the formula will give me the value from cell I1.

The problem that I am having is if I want to pick up the value from the cell
below (e.g. I3), I can use the Hlookup formula like this
"hlookup("A",$G$2:$M$2,2,false), but in my current situation, I am looking up
the value, instead of looking down.

Thanks.

 

[Luke M]

Small version:
=INDEX(G1:M1,MATCH("A",G2:M2,0))

If there's a possibility of "A" not being found:
=IF(COUNTIF(G2:M2,"A"),INDEX(G1:M1,MATCH("A",G2:M2,0)),"")

 

[Dave Peterson]

=index(g1:m1,match("A",g2:m2,0))

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[ILoveExcel]

Hi.
I recommend trying a formula that combines INDEX and MATCH formulas. I
consider this combination to be a breakthrough invention and the most
powerful combination of finding and returning data between workbooks, sheets,
or ranges on the sheet. Unlike VLOOKUP or HLOOKUP, the data do not have to
be in sorted order. In combination, MATCH returns the row or column position
of a cell based on the cell contents, and INDEX return the value of a cell
based on column and or row positions identified in the MATCH statement.

Enter this formula anywhere on the worksheet or add the sheet name where the
data are stored to use the formula on another sheet.
"=INDEX(3:3,,MATCH("A",2:2,0))"

If "A" is in column D, the MATCH returns 4, and the formula resolved to the
value in cell D3.

Understand the formula from the inside parenthesis first:
MATCH("A",2:2,0) finds a cell containing the first argument (i.e. "A")
in the second argument (i.e. 2:2) and the third argument (ie. value of 0) is
a switch saying find the exact match.
Note that Match returns the position of the first cell containing "A"
reading down the row. e.g. if "A" is in column D, the result of this MATCH
is a value 4.

INDEX(3:3,,4) returns the value of the cell in the range given as the
first argument (i.e. row 3), at the row index position within the range given
in the second argument (i.e. null because your reading only a row) and at the
column inde position within the range given in the third argument (i.e. 4).

This is a powerful formula with many nuances worth learning. The above
formula used as written will solve your problem for now.

Finally, notice that I wrote the formula to look in the entire row, not just
the range given in your question. I find the formula is less prone to error
this way/. However, if you are entering these formulas in columns A:G for
example where "A" may occur before column G, use:
"=INDEX(G3:M3,,MATCH("A",G2:M2,0))"

When using this less than whole row or column references, you must remember
to use the equivalent ranges for the match as in the index formula, since
INDEX runs from relative position in the range.

Good luck.

 

[Accesshelp]

Luke,

Thank you very much for the formula. I end up using the second formula.
You were very thoughtful.

Have a great holiday season!

 

[Accesshelp]

ILoveExcel,

Thank you very much for the formula and detail explanations. Your formula
and explanations work perfectly.

Have a great holiday season!

 

[Accesshelp]

Dave,

Thank you very much for the formula and additional references. Your formula
works perfectly.

Have a great holiday season!