I'm not sure that I understood you correctly, but try the following...

H6, copied to I6 and J6:

=INDEX($C$2:$C$6,MATCH(1,($B$2:$B$6=$A6)*($G$2:$G$ 6=D6),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
liz217 wrote:

I'm an intermediate spreadsheet writer, but this one's got me clueless.

My table looks like this:

A B C D E F G
1 From To Elev. IN 1 IN 2 IN 3 OUT
2 1 2 1181.50 East
3 2 3 1177.85 East East
4 3 5 1171.68 East East
5 4 5 1171.90 North
6 5 10 1166.54 East North East


In cell(H6), I need to find the value in cell(A6) in column B and return the
value in column C for that row such that the value in column G for the
corresponding row matches the value in cell(D6). (The next two columns will
do the same comparison for columns E6 & F6.)

In this example, I need to find the value 5 in column B. There are two
entries. I am looking for the Out Flow (column G) that matches the In Flow
(column D, row 6), "East." It is 1171.68, cell(C4). {The next column will
look for the 5 in Column B that has Out Flow equal to "North" (to match
column E, row 6), which is 1171.90, or cell(C5).}

This is really complicated and I've read some strings about Index & Match,
but still not sure how to apply them specifically to my case. If I can get
this one cell working correctly, I can expand the formula to the remainder of
my spreadsheet.

Thanks in advance for taking the time to look at this.

Liz


Thanks,

Liz