Lookup??

[pepjr]

Hi, Stuck....

I have 4 rows of data (going across lots of columns). Top row is a series of dates. Row's 2-4 have a set of values in there (just names, each appearing once).

Want to find the cell (in row 2-3) with a given name in, then return the date (from the top row of that column).

Can anyone help?

 

[arthurbr]

Try this http://www.exceluser.com/formulas/how-to-use-sumproduct-to-create-two-dimensional-lookups.htm

 

[PatOBrien198]

Try this...

=INDEX($C$1:$M$1,1,MATCH(A1,$C$2:$M$2,0))

Name to match in A1.
Dates in C1 to M1 (expand as necessary)
Names to search thru in C2 thru M2 (expand as necessary.)

 

[steve the fish]

Possible way:

=INDEX($A$1:$F$1,MIN(IF($A$2:$F$4=A6,COLUMN($A$2:$F$4)-COLUMN($A$1)+1)))

Needs CTRL-SHIFT-ENTER to commit to cell.

 

[steve the fish]

Actually that doesnt work if the value isnt found. This is better:

=IF(COUNTIF($A$2:$F$4,$A$6)>0,INDEX($A$1:$F$1,MIN(IF($A$2:$F$4=$A$6,COLUMN($A$2:$F$4)-COLUMN($A$1)+1))),"Not Found")

 

[pepjr]

Actually that doesnt work if the value isnt found. This is better:

=IF(COUNTIF($A$2:$F$4,$A$6)>0,INDEX($A$1:$F$1,MIN(IF($A$2:$F$4=$A$6,COLUMN($A$2:$F$4)-COLUMN($A$1)+1))),"Not Found")

Sweet! Works like a charm. Cheers Pal.