|
Looking up values in a table
I have a table containing prices of a product with the width across the
columns and length down the rows looking something like this; 25 30 35 40 25 4.60 5.20 6.35 7.10 30 5.15 6.20 7.85 8.30 35 6.15 7.50 8.30 9.20 What I am looking for is for the user to enter values in 2 cells (width and length), and have the price returned for the product in the price cell. eg; if the user enters width 32 and length 26 it would return 7.85 in the price cell (it must always round up not down) I hope you understand what I want. Thanks in anticipation. |
Assuming your table is in A1:E4, and the lookup length is in H1, and the
lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... I have a table containing prices of a product with the width across the columns and length down the rows looking something like this; 25 30 35 40 25 4.60 5.20 6.35 7.10 30 5.15 6.20 7.85 8.30 35 6.15 7.50 8.30 9.20 What I am looking for is for the user to enter values in 2 cells (width and length), and have the price returned for the product in the price cell. eg; if the user enters width 32 and length 26 it would return 7.85 in the price cell (it must always round up not down) I hope you understand what I want. Thanks in anticipation. |
Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any
other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Do all of your lookup values exist in the row/column headings? If not, what
would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Yes, the lookup values do exist in the row/column headings.
"Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
I used your data in my test, and it worked fine. What values do you have in
H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
30 in both, does it matter that the table is on a different sheet named
matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Yes it does, in that case, try
=INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
I must be thick! I should have spotted that. Thats for the help Bob, that
works great now. "Bob Phillips" wrote: Yes it does, in that case, try =INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Glad you've got it sorted.
Bob "LesLdh" wrote in message ... I must be thick! I should have spotted that. Thats for the help Bob, that works great now. "Bob Phillips" wrote: Yes it does, in that case, try =INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
If you arrange your lengths and widths in descending order, you won't
need all the values. For example, with the table changed to: 40 35 30 25 35 9.2 8.3 7.5 6.15 30 8.3 7.85 6.2 5.15 25 7.1 6.35 5.2 4.6 Use the formula: =INDEX(Matrix!B2:E4,MATCH(H1,Matrix!A2:A4,-1),MATCH(H2,Matrix!B1:E1,-1)) LesLdh wrote: Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1, 0)) -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Bob- do you know a lot about index tables? Do you know if there is a way to
retrieve data if there is more than two contants? "Bob Phillips" wrote: Glad you've got it sorted. Bob "LesLdh" wrote in message ... I must be thick! I should have spotted that. Thats for the help Bob, that works great now. "Bob Phillips" wrote: Yes it does, in that case, try =INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Ashley,
Do you mean two values to lookup? If so, you could concatenate them, something like =INDEX(A1:A100,MATCH("Bill"&"Jones",B1:B100&C1:C10 0,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ashley" wrote in message ... Bob- do you know a lot about index tables? Do you know if there is a way to retrieve data if there is more than two contants? "Bob Phillips" wrote: Glad you've got it sorted. Bob "LesLdh" wrote in message ... I must be thick! I should have spotted that. Thats for the help Bob, that works great now. "Bob Phillips" wrote: Yes it does, in that case, try =INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
no, I'm meaning more like, if lastname = jackson and first name = bob and
position = analyst, then return X. Of course then data would be in a table. make sense? "Bob Phillips" wrote: Ashley, Do you mean two values to lookup? If so, you could concatenate them, something like =INDEX(A1:A100,MATCH("Bill"&"Jones",B1:B100&C1:C10 0,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ashley" wrote in message ... Bob- do you know a lot about index tables? Do you know if there is a way to retrieve data if there is more than two contants? "Bob Phillips" wrote: Glad you've got it sorted. Bob "LesLdh" wrote in message ... I must be thick! I should have spotted that. Thats for the help Bob, that works great now. "Bob Phillips" wrote: Yes it does, in that case, try =INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Some of your numbers may in fact be text.
-- Ron P Sometimes you're the windshield:) Sometimes you're the bug:( "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Ashley,
I am still reading that as a two value lookup. For example a table that looks like Jackson Joe Singer Jackson Bob Analyst Wilson Bob Boss To get the one you mention, you could use =INDEX(C1:C100,MATCH("Jackson"&"Bob",A1:A100&B1:B1 00,0)) and that returns Analyst -- HTH RP (remove nothere from the email address if mailing direct) "Ashley" wrote in message ... no, I'm meaning more like, if lastname = jackson and first name = bob and position = analyst, then return X. Of course then data would be in a table. make sense? "Bob Phillips" wrote: Ashley, Do you mean two values to lookup? If so, you could concatenate them, something like =INDEX(A1:A100,MATCH("Bill"&"Jones",B1:B100&C1:C10 0,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ashley" wrote in message ... Bob- do you know a lot about index tables? Do you know if there is a way to retrieve data if there is more than two contants? "Bob Phillips" wrote: Glad you've got it sorted. Bob "LesLdh" wrote in message ... I must be thick! I should have spotted that. Thats for the help Bob, that works great now. "Bob Phillips" wrote: Yes it does, in that case, try =INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Thanks bob -- my example was a bad one.. But the more I thought about the
concatenating, you were right--- it would work. I just had to name the drop down cells so that I could concatenate. I appreciate your help "Bob Phillips" wrote: Ashley, I am still reading that as a two value lookup. For example a table that looks like Jackson Joe Singer Jackson Bob Analyst Wilson Bob Boss To get the one you mention, you could use =INDEX(C1:C100,MATCH("Jackson"&"Bob",A1:A100&B1:B1 00,0)) and that returns Analyst -- HTH RP (remove nothere from the email address if mailing direct) "Ashley" wrote in message ... no, I'm meaning more like, if lastname = jackson and first name = bob and position = analyst, then return X. Of course then data would be in a table. make sense? "Bob Phillips" wrote: Ashley, Do you mean two values to lookup? If so, you could concatenate them, something like =INDEX(A1:A100,MATCH("Bill"&"Jones",B1:B100&C1:C10 0,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ashley" wrote in message ... Bob- do you know a lot about index tables? Do you know if there is a way to retrieve data if there is more than two contants? "Bob Phillips" wrote: Glad you've got it sorted. Bob "LesLdh" wrote in message ... I must be thick! I should have spotted that. Thats for the help Bob, that works great now. "Bob Phillips" wrote: Yes it does, in that case, try =INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
Bob,
Maybe you can expand a little on your formula to help me. I have a similar situation with one exception. His data had an x,y type matrix. I have four columns, I need to match column A and B and retrieve column C value in another worksheet. I have been manipulating your formula without success. Thank you in advance for your help. So below, you will see the same number in column A repeated, so I use column E (cpft) for that second match and then retrieve column F (copt). The end result I hope for is a matrix type x,y data table with one occurence of cpva, all the cpft across the top with the corresponding value (copt) in the x,y coordinate. MS Access crashes because of too many crosstab queries. cpva oqua item cpft copt 355499 1 G.5590 prctbl current 355499 1 G.5590 cust dom 355499 1 G.5590 fammod 5590 "Ashley" wrote: Thanks bob -- my example was a bad one.. But the more I thought about the concatenating, you were right--- it would work. I just had to name the drop down cells so that I could concatenate. I appreciate your help "Bob Phillips" wrote: Ashley, I am still reading that as a two value lookup. For example a table that looks like Jackson Joe Singer Jackson Bob Analyst Wilson Bob Boss To get the one you mention, you could use =INDEX(C1:C100,MATCH("Jackson"&"Bob",A1:A100&B1:B1 00,0)) and that returns Analyst -- HTH RP (remove nothere from the email address if mailing direct) "Ashley" wrote in message ... no, I'm meaning more like, if lastname = jackson and first name = bob and position = analyst, then return X. Of course then data would be in a table. make sense? "Bob Phillips" wrote: Ashley, Do you mean two values to lookup? If so, you could concatenate them, something like =INDEX(A1:A100,MATCH("Bill"&"Jones",B1:B100&C1:C10 0,0)) -- HTH RP (remove nothere from the email address if mailing direct) "Ashley" wrote in message ... Bob- do you know a lot about index tables? Do you know if there is a way to retrieve data if there is more than two contants? "Bob Phillips" wrote: Glad you've got it sorted. Bob "LesLdh" wrote in message ... I must be thick! I should have spotted that. Thats for the help Bob, that works great now. "Bob Phillips" wrote: Yes it does, in that case, try =INDEX(matrix!A1:E4,MATCH(H1,matrix!A1:A4,0),MATCH (H2,matrix!A1:E1,0)) as both the INDEX and both MATCH functions are using that table -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... 30 in both, does it matter that the table is on a different sheet named matrix? I amended your formula to say =INDEX(matrix!A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1: E1,0)) "Bob Phillips" wrote: I used your data in my test, and it worked fine. What values do you have in H1 and H2? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Yes, the lookup values do exist in the row/column headings. "Bob Phillips" wrote: Do all of your lookup values exist in the row/column headings? If not, what would you expect to get? -- HTH RP (remove nothere from the email address if mailing direct) "LesLdh" wrote in message ... Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any other ideas. "Bob Phillips" wrote: Assuming your table is in A1:E4, and the lookup length is in H1, and the lookup width is in H2, try this =INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0)) |
| All times are GMT +1. The time now is 01:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com