Looking up values in a table

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any
other ideas.

 

[Bob Phillips]

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)

 

[Guest]

Yes, the lookup values do exist in the row/column headings.

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

I must be thick! I should have spotted that. Thats for the help Bob, that
works great now.

 

[Bob Phillips]

Glad you've got it sorted.

Bob

I must be thick! I should have spotted that. Thats for the help Bob, that
works great now.

 

[Debra Dalgleish]

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))

 

[Guest]

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]

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:C100,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

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?

 

[Ron]

Some of your numbers may in fact be text.
--

Ron P

Sometimes you're the windshield
Sometimes you're the bug

 

[Bob Phillips]

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:B100,0))

and that returns Analyst

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

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

 

[Guest]

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