INDEX & MATCH find first column value in an array?

[Ambie]

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,'WorksheetAARRAY',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!

 

[Bernard Liengme]

Try using HLOOKUP
HLOOKUP(lookup_value,table_array,row_index_num,range_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

 

[Shane Devenshire]

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]

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