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