Lookup (more complicated)

  • Thread starter Thread starter F Tahbaz
  • Start date Start date
F

F Tahbaz

Can someone help me figure out the way to handle this.

Four columns of data
11 rows of data

Column A = {A,B,A,C,D,A,B,B,C,D,A}
Column B = {Y,N,Y,Y,N,N,N,Y,Y,N,Y}
Column C = {1,2,4,7,5,3,5,2,1,6,8}

Take the last row (#11) of data as an example = {A,Y,8}
Column D is where I need to get the formula to return this:

Show me the last value in Column C where Column A="A", and Column
B="Y"

In this example, that value would be the 3rd row which is 4.
 
F said:
Can someone help me figure out the way to handle this.

Four columns of data
11 rows of data

Column A = {A,B,A,C,D,A,B,B,C,D,A}
Column B = {Y,N,Y,Y,N,N,N,Y,Y,N,Y}
Column C = {1,2,4,7,5,3,5,2,1,6,8}

Take the last row (#11) of data as an example = {A,Y,8}
Column D is where I need to get the formula to return this:

Show me the last value in Column C where Column A="A", and Column
B="Y"

In this example, that value would be the 3rd row which is 4.
Click to expand...

Wouldn't it be the 11th row (8)?
 
Hello,

I assumed that your data begin at row 2 and end at row 12 (A2:C12)

Enter this array formula into D3 (leave D2 empty)
=INDEX(C:C,1/(1/MAX(($A$2:A2=A3)*($B$2:B2=B3)*ROW($C$2:C2))))

This formula is an array formula : It should be validated
by the combination of the three keys: Ctrl+Shift+Enter
instead with the single key Enter.

Then drag down the formula from D3 to D12

This formula will return #DIV/0! if there is no match
in the lines above the current line (where the formula is)

Another array formula that avoid the division by 0 could be:
=INDEX(C:C,IF(MAX(($A$2:A2=A3)*($B$2:B2=B3)*ROW($C$2:C2))=0,NA(),MAX(($A$2:A2=A3)*($B$2:B2=B3)*ROW($C$2:C2))))





"F Tahbaz" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...
 
Back
Top