Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
LesLdh
 
Posts: n/a
Default 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.
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.



  #3   Report Post  
LesLdh
 
Posts: n/a
Default

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


  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

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




  #5   Report Post  
LesLdh
 
Posts: n/a
Default

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







  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

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







  #7   Report Post  
LesLdh
 
Posts: n/a
Default

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








  #8   Report Post  
Bob Phillips
 
Posts: n/a
Default

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










  #9   Report Post  
LesLdh
 
Posts: n/a
Default

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











  #10   Report Post  
Bob Phillips
 
Posts: n/a
Default

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















  #11   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #12   Report Post  
Ashley
 
Posts: n/a
Default

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














  #13   Report Post  
Bob Phillips
 
Posts: n/a
Default

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
















  #14   Report Post  
Ashley
 
Posts: n/a
Default

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

















  #15   Report Post  
Ron
 
Posts: n/a
Default

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





  #16   Report Post  
Bob Phillips
 
Posts: n/a
Default

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



















  #17   Report Post  
Ashley
 
Posts: n/a
Default

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




















  #18   Report Post  
jwh
 
Posts: n/a
Default

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




















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Distinct Values by Group Using Pivot Table (NM) MCP Excel Worksheet Functions 3 February 11th 05 10:22 PM
Pivot Table with Zero Values for Month wyman Charts and Charting in Excel 1 January 14th 05 06:59 PM
table dow Excel Worksheet Functions 0 January 11th 05 06:17 PM
how can i fill a table with values from repeated regressions buragotch Excel Worksheet Functions 4 January 1st 05 02:25 PM
Sum minimum values in a pivot table AK Excel Worksheet Functions 1 December 22nd 04 09:55 PM


All times are GMT +1. The time now is 11:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"