Lookup in a 2 dimensional table

  • Thread starter Thread starter Huck Rorick
  • Start date Start date
H

Huck Rorick

I want to look up a value in a table. I would like to specify a value which
determines which column to look in and a value that determines which row and
then return the value in that row and column.



For example, I have this table (not a real table) that shows the
friction/pressure loss in a pipe depending on its size and flow in
gallons/minute (GPM). Given a pipe size and a flow in GPM, I want to return
the value from the table.




1 inch
2 inch
3 inch

1 gpm
5
4
3

2 gpm
4
3
2

3 gpm
3
2
1




Anyone have an idea of how to do this?



Thanks,



Huck
 
Let the pipe sizes be in A1:E1, the flow values in A2:A10, and the data in
B2:K10
Then in G1 enter the required pipe size and in H1 the required flow; the
lookup is performed with
=INDEX(B2:E10,MATCH(H1,A2:A10,1),MATCH(G1,B1:E1,1))
Experiment with =MATCH(G1,B1:E1,1) and see how it works
You may need to change the last arguments (the 1) to 0 or -1 depending on
the problem
best wishes
 
Back
Top