If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Search columns and rows for values to return common value
I have a table that I would like to retrieve data from by
using two different user inputs to specify the values in the start of the columns and rows. I want the formula to search for the value in the colums and the rows. Not by specific columns or rows. Example: A1 SIZE A2 10 A3 18 A4 30 B1 JA C1 JK D1 LJ E1 HT B2 15 B3 22 B4 27 C2 19 C3 26 C4 32 In two other cells a user might enter, 18 JK Then in another cell I want to have the two user entries to search the tables Rows and colums to return the value from cell C3 or 26. Then another user might enter, 10 JA This should return the value from cell B2 or 15. Any ideas? Thanks. |
#2
|
|||
|
|||
Search columns and rows for values to return common value
See http://www.cpearson.com/excel/lookups.htm for double
lookups -----Original Message----- I have a table that I would like to retrieve data from by using two different user inputs to specify the values in the start of the columns and rows. I want the formula to search for the value in the colums and the rows. Not by specific columns or rows. Example: A1 SIZE A2 10 A3 18 A4 30 B1 JA C1 JK D1 LJ E1 HT B2 15 B3 22 B4 27 C2 19 C3 26 C4 32 In two other cells a user might enter, 18 JK Then in another cell I want to have the two user entries to search the tables Rows and colums to return the value from cell C3 or 26. Then another user might enter, 10 JA This should return the value from cell B2 or 15. Any ideas? Thanks. . |
#3
|
|||
|
|||
Search columns and rows for values to return common value
"Dale" wrote in message
... I have a table that I would like to retrieve data from by using two different user inputs to specify the values in the start of the columns and rows. I want the formula to search for the value in the colums and the rows. Not by specific columns or rows. Example: A1 SIZE A2 10 A3 18 A4 30 B1 JA C1 JK D1 LJ E1 HT B2 15 B3 22 B4 27 C2 19 C3 26 C4 32 In two other cells a user might enter, 18 JK Then in another cell I want to have the two user entries to search the tables Rows and colums to return the value from cell C3 or 26. Then another user might enter, 10 JA This should return the value from cell B2 or 15. Any ideas? Thanks. Supposing your two cells for user entry are G1 and H1: =INDEX(A1:E4,MATCH(G1,INDEX(A1:E4,,1),0),MATCH(H1, INDEX(A1:E4,1,),0)) |
Thread Tools | |
Display Modes | |
|
|