I have an array of data with the first column and the first row being
sizes and the contents of the table being price.

I want to have a drop list where the user selects size a, size b and
gets given the price.

I have successfully done this with HLOOKUP however while the first list
is the values of the first row the second list is the index 1,2,3,4,
etc not the sizes in the first list as I wanted.

How can I get the drop down list to display the actual data from the
first column while returning the index number as required by the
HLOOKUP formula?

Thanks,

Martin

I think you want INDEX MATCH

=INDEX(full_range,MATCH(row_value,first_column,0), MATCH(Col_value,first_row,
0))

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Martin Smith" wrote in
message ...
I have an array of data with the first column and the first row being
sizes and the contents of the table being price.

I want to have a drop list where the user selects size a, size b and
gets given the price.

I have successfully done this with HLOOKUP however while the first list
is the values of the first row the second list is the index 1,2,3,4,
etc not the sizes in the first list as I wanted.

How can I get the drop down list to display the actual data from the
first column while returning the index number as required by the
HLOOKUP formula?

Thanks,

Martin

On 2006-05-30 13:38:00 +0100, "Bob Phillips" said:

I think you want INDEX MATCH

=INDEX(full_range,MATCH(row_value,first_column,0), MATCH(Col_value,first_row,
0))

I'll give it a try.

Thanks.

On 2006-05-30 14:23:08 +0100, Martin Smith
said:

On 2006-05-30 13:38:00 +0100, "Bob Phillips" said:

I think you want INDEX MATCH

=INDEX(full_range,MATCH(row_value,first_column,0), MATCH(Col_value,first_row,
0))

I'll give it a try.

Thanks.

Works a treat once I realised that the first row and column should be
omitted from the full_range

No, you can include them as well as long as you include the first column in
the first row and vice versa.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Martin Smith" wrote in
message ...
On 2006-05-30 14:23:08 +0100, Martin Smith
said:

On 2006-05-30 13:38:00 +0100, "Bob Phillips"
said:

I think you want INDEX MATCH


=INDEX(full_range,MATCH(row_value,first_column,0), MATCH(Col_value,first_row,
0))

I'll give it a try.

Thanks.

Works a treat once I realised that the first row and column should be
omitted from the full_range

On 2006-05-30 16:30:52 +0100, "Bob Phillips" said:

No, you can include them as well as long as you include the first column in
the first row and vice versa.

When I first specified the full array it was off by one cell across and
down? or was that up? I can't remember but I'll go and take another
look. That said the sheet seems to be working ok.