I need to lookup a value in cell D15 from worksheet B in an array found in
worksheet A for the first column containing the value of cell D15 from
worksheet B. Within one column of worksheet A the value of cell D15 may exist
more than one time. I need the function to return the value in row 2 of this
column. I have tried using this
"=INDEX('WorksheetAARRAY',MATCH(D15,'WorksheetAARR AY',0),1) but this only
works if Match is one column or one row. It is not possible to up the sheet
this way. Please help!

Try using HLOOKUP
HLOOKUP(lookup_value,table_array,row_index_num,ran ge_lookup)

=HLOOKUP(D15,WorksheetA!Array,2,TRUE)

The last argument specifies if an exact or a near match is required

Come back if you have more questions
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ambie" wrote in message
...
I need to lookup a value in cell D15 from worksheet B in an array found in
worksheet A for the first column containing the value of cell D15 from
worksheet B. Within one column of worksheet A the value of cell D15 may
exist
more than one time. I need the function to return the value in row 2 of
this
column. I have tried using this
"=INDEX('WorksheetAARRAY',MATCH(D15,'WorksheetAARR AY',0),1) but this only
works if Match is one column or one row. It is not possible to up the
sheet
this way. Please help!

Hi,

You could have used INDEX & MATCH as follows

=INDEX(Sheet3!A2:M2,,MATCH(D15,Sheet3!A1:M1,0))

Keep in mind the MATCH function only looks at a single row or single column,
not a two dimensional range.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Ambie" wrote:

I need to lookup a value in cell D15 from worksheet B in an array found in
worksheet A for the first column containing the value of cell D15 from
worksheet B. Within one column of worksheet A the value of cell D15 may exist
more than one time. I need the function to return the value in row 2 of this
column. I have tried using this
"=INDEX('WorksheetAARRAY',MATCH(D15,'WorksheetAARR AY',0),1) but this only
works if Match is one column or one row. It is not possible to up the sheet
this way. Please help!

Sorry I should have specified that the data in the table is not sorted and
what I want is essentially a reverse hlookup. for example if the data was
like below, I want the first column containing an instance of the value AAA
(in this case column2). From this column, I want to return the value found
in row 2 (02/1/2008).

row1 Column1 Column2 Column2 Column3
row2 1/1/2008 02/1/2008 03/1/2008 04/01/2008
row3 BBB AAA AAA CCC
row 4 CCC AAA BBB AAA

"Bernard Liengme" wrote:

Try using HLOOKUP
HLOOKUP(lookup_value,table_array,row_index_num,ran ge_lookup)

=HLOOKUP(D15,WorksheetA!Array,2,TRUE)

The last argument specifies if an exact or a near match is required

Come back if you have more questions
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ambie" wrote in message
...
I need to lookup a value in cell D15 from worksheet B in an array found in
worksheet A for the first column containing the value of cell D15 from
worksheet B. Within one column of worksheet A the value of cell D15 may
exist
more than one time. I need the function to return the value in row 2 of
this
column. I have tried using this
"=INDEX('WorksheetAARRAY',MATCH(D15,'WorksheetAARR AY',0),1) but this only
works if Match is one column or one row. It is not possible to up the
sheet
this way. Please help!