Extract data value using pick list
 

Hi Groupies

I have a spreadsheet similar to the following example:

H1 H2 H3
A101 A E I
A102 B F J
A103 C G K
A104 D H L

I would like to use a couple of pick lists to be able to select a row and
another to select a column and then have Excel return the intersecting value.
So, if in the first list I select A103 and in the second one I select H2, I
would have G in my found value.

I was thinking that the Index function would work, but I am just getting
#REF in my result. I was sure that I have done this before, but perhaps not.

Thanks in Advance.

--
Thanks for the brainwaves!

CJ
I blame the parents........

Extract data value using pick list
 

Assuming you already know how to use Data Validation to create a pick list...
Try this:

With
A1:D5 containing your posted table
and
F1 contains the dropdown holding A101, A102, etc
and
G1 contains the dropdown holding H1, H2, etc

Then
H1:
=IF(AND(COUNTIF($A$1:$A$5,F1),COUNTIF($A$1:$D$1,G1 )),VLOOKUP(F1,$A$1:$D$5,MATCH(G1,$A$1:$D$1,0),0)," no match")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"CJ" wrote:

Hi Groupies

I have a spreadsheet similar to the following example:

H1 H2 H3
A101 A E I
A102 B F J
A103 C G K
A104 D H L

I would like to use a couple of pick lists to be able to select a row and
another to select a column and then have Excel return the intersecting value.
So, if in the first list I select A103 and in the second one I select H2, I
would have G in my found value.

I was thinking that the Index function would work, but I am just getting
#REF in my result. I was sure that I have done this before, but perhaps not.

Thanks in Advance.

--
Thanks for the brainwaves!

CJ
I blame the parents........

Extract data value using pick list
 

Hi

assuming your table starts at A1 and is in the range A1:D5, and you
enter your A103 in F1 and you H2 in G1 then

=INDEX($A$1:$D$5,MATCH(F1,$A$1:$A$5,0),MATCH(G1,$A $1:$D$1,0))

--
Regards

Roger Govier


"CJ" wrote in message
...
Hi Groupies

I have a spreadsheet similar to the following example:

H1 H2 H3
A101 A E I
A102 B F J
A103 C G K
A104 D H L

I would like to use a couple of pick lists to be able to select a row
and
another to select a column and then have Excel return the intersecting
value.
So, if in the first list I select A103 and in the second one I select
H2, I
would have G in my found value.

I was thinking that the Index function would work, but I am just
getting
#REF in my result. I was sure that I have done this before, but
perhaps not.

Thanks in Advance.

--
Thanks for the brainwaves!

CJ
I blame the parents........

Extract data value using pick list
 

And the winner is.........Roger!!

Thanks for the input from both of you. Ron, I'm not sure what the problem
is, but I always get No Match so ......hhhmmmmm.

Thanks for the quick responses.
--
Thanks for the brainwaves!

CJ
I blame the parents........


"Roger Govier" wrote:

Hi

assuming your table starts at A1 and is in the range A1:D5, and you
enter your A103 in F1 and you H2 in G1 then

=INDEX($A$1:$D$5,MATCH(F1,$A$1:$A$5,0),MATCH(G1,$A $1:$D$1,0))

--
Regards

Roger Govier


"CJ" wrote in message
...
Hi Groupies

I have a spreadsheet similar to the following example:

H1 H2 H3
A101 A E I
A102 B F J
A103 C G K
A104 D H L

I would like to use a couple of pick lists to be able to select a row
and
another to select a column and then have Excel return the intersecting
value.
So, if in the first list I select A103 and in the second one I select
H2, I
would have G in my found value.

I was thinking that the Index function would work, but I am just
getting
#REF in my result. I was sure that I have done this before, but
perhaps not.

Thanks in Advance.

--
Thanks for the brainwaves!

CJ
I blame the parents........