Looking for a way to look up a value using row name and varying columns.

[apickens]

Hello,

What I am trying to accomplish is to be able to pull information from a worksheet by searching for a column name, and then finding a value in that column by the name of the row. When i say "name" of the column and row, i do not mean row 1-n or columns A-Z, i would like it to search by the names i assign to my data.

Ex.)


Columns A1-A8, Rows B1-B8. I want to pull the value of AxBx by searching Ax and Bx independently. I attempted this with a combination of HLOOKUP, and VLOOKUP, but got confused when it came to the indexing. If this makes any sense, any help is appreciated. If further explanation is required, feel free ask.


Thank you,
Drew

[Richard Buttrey]

Hi,

Can you offer a worked example in an Excel workbook? Preferably the production workbook.

[apickens]

Test.JPG

I cant provide the actual information but here is an example.

What i have is a report that will always have Red, Yellow, Orange, etc. and all of their related properties. However, the columns and rows are not always in the same order. What i would like is to create a worksheet with built in formulas that can be placed in the workbook of this report that searches the work sheet for a column name, and then pulls the data from the specified row name of that column. i.e. Temperature of Red, Flow rate of Green, Pressure of Yellow, etc

[Jonmo1]

Try this assuming that table begins in A1

=INDEX($B$2:$H$8,MATCH("Mass Flow",$A$2:$A$8,0),MATCH("Yellow",$B$1:$H$1,0))

You can replace "Mass Flow" and "Yellow" with cell references like
=INDEX($B$2:$H$8,MATCH(N2,$A$2:$A$8,0),MATCH(M1,$B$1:$H$1,0))
N2 = Mass Flow (or whatever row value)
M1 = Yellow (or whatever column value)

[apickens]

That Worked! Thank you Jonmo, your assistance is greatly appreciated.

[Jonmo1]

You're welcome