Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look Up two cells
I have the following table I want to set up in Excel. My question is if a
user keys a Spec and a Spec Gravity number, how do I get Excel to look up the corresponding intersection of the two numbers. Spec Spec Gravity 1.01 1.02 1.03 1.04 1.2 1058TT 3.38 3.343 3.35 3.7 3.94 2534C 4.58 4.67 4.75 4.8 5.08 1058TC 3.97 3.99 4.02 4.05 4.75 What kind of formula would work best for this application. Thanks -- Kim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look Up two cells
Say you enter your Spec criteria in J1,
And your Gravity criteria in J2, Then try this formula: =INDEX(B3:F5,MATCH(J1,A3:A5,0),MATCH(J2,B2:F2,0)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Kim" wrote in message ... I have the following table I want to set up in Excel. My question is if a user keys a Spec and a Spec Gravity number, how do I get Excel to look up the corresponding intersection of the two numbers. Spec Spec Gravity 1.01 1.02 1.03 1.04 1.2 1058TT 3.38 3.343 3.35 3.7 3.94 2534C 4.58 4.67 4.75 4.8 5.08 1058TC 3.97 3.99 4.02 4.05 4.75 What kind of formula would work best for this application. Thanks -- Kim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look Up two cells
Assuming your table is on a separate tab, on the tab you are looking for the
correct reference, would be as follows. Also assuming that on the Data tab that column A has your Spec and row 1 has your Spec Gravity: In the cell they are choosing the Spec for go to Data--Validation, choose List, and then in the Source box type: =INDIRECT("Data!A:A") In the cell they are choosing for the Spec Gravity for go to Data--Validation, choose List, and then in the Source box type: =INDIRECT("Data!1:1") Then in the cell where you want to display the correct value type: =VLOOKUP(Spec,Data!A1:F100,MATCH(SpecGravity,Data! 1:1,0),FALSE) -- John C "Kim" wrote: I have the following table I want to set up in Excel. My question is if a user keys a Spec and a Spec Gravity number, how do I get Excel to look up the corresponding intersection of the two numbers. Spec Spec Gravity 1.01 1.02 1.03 1.04 1.2 1058TT 3.38 3.343 3.35 3.7 3.94 2534C 4.58 4.67 4.75 4.8 5.08 1058TC 3.97 3.99 4.02 4.05 4.75 What kind of formula would work best for this application. Thanks -- Kim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions |