Lookup a value using two columns

[Guest]

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

 

[Domenic]

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!

 

[Herbert Seidenberg]

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

 

[Guest]

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