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

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

Here is my interpretation of the problem
From To Elev in_1 in_2 in_3 out
1 9 200 A C D A . . .
2 8 111 B A . B 143 191 176
3 5 109 . A C C . . .
4 4 114 B . C B 114 . .
5 8 147 B C D C . . 109
6 2 191 . C D C . . .
7 2 176 B A C C . . .
8 9 171 C C D D 111 147 .
9 2 143 C A . C 200 . 171
H=lookup From in To and give me Elev if out=in_1 in that row
I=lookup From in To and give me Elev if out=in_2 in that row
J=lookup From in To and give me Elev if out=in_3 in that row

Select all the data and headers and
Insert Name Create Top Row
Also define these names
Names in Workbook com1 Refers to =To*IF(out=in_1,1,0)
Names in Workbook com2 Refers to =To*IF(out=in_2,1,0)
Names in Workbook com3 Refers to =To*IF(out=in_3,1,0)
In columns H, I, J enter respectively
=IF(ISNA(INDEX(Elev,MATCH(From,com1,0))),".",INDEX (Elev,MATCH(From,com1,0)))
=IF(ISNA(INDEX(Elev,MATCH(From,com2,0))),".",INDEX (Elev,MATCH(From,com2,0)))
=IF(ISNA(INDEX(Elev,MATCH(From,com3,0))),".",INDEX (Elev,MATCH(From,com3,0)))

Thank you for your response. You correctly understand the problem. I tried
to do what you said, but I got dots in all my cells, so I was missing some
logic somewhere. I still do not understand how the array formulas work, so I
will have to do some more learning. I ended up using the concatenate formula
to combine the Out and To columns. And then I used a vlookup for each of the
In columns. It took several columns to get the correct results, so I just
hid all the "behind the scenes" columns. I was hoping for something more
straightforward, but this is working.

Thanks,

Liz


"Herbert Seidenberg" wrote:

Here is my interpretation of the problem
From To Elev in_1 in_2 in_3 out
1 9 200 A C D A . . .
2 8 111 B A . B 143 191 176
3 5 109 . A C C . . .
4 4 114 B . C B 114 . .
5 8 147 B C D C . . 109
6 2 191 . C D C . . .
7 2 176 B A C C . . .
8 9 171 C C D D 111 147 .
9 2 143 C A . C 200 . 171
H=lookup From in To and give me Elev if out=in_1 in that row
I=lookup From in To and give me Elev if out=in_2 in that row
J=lookup From in To and give me Elev if out=in_3 in that row

Select all the data and headers and
Insert Name Create Top Row
Also define these names
Names in Workbook com1 Refers to =To*IF(out=in_1,1,0)
Names in Workbook com2 Refers to =To*IF(out=in_2,1,0)
Names in Workbook com3 Refers to =To*IF(out=in_3,1,0)
In columns H, I, J enter respectively
=IF(ISNA(INDEX(Elev,MATCH(From,com1,0))),".",INDEX (Elev,MATCH(From,com1,0)))
=IF(ISNA(INDEX(Elev,MATCH(From,com2,0))),".",INDEX (Elev,MATCH(From,com2,0)))
=IF(ISNA(INDEX(Elev,MATCH(From,com3,0))),".",INDEX (Elev,MATCH(From,com3,0)))