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