CODEWITHSUNDEEP
excelexcel-formula
Felton Wang
Felton Wang

Reputation: 173

find cell value after matching for row number and column number

I want to find which user is "P" given a date in data below.

I've tried retrieving the row number of the given date using "=MATCH(A5,$A$4:$A$5,0)" and also the column number of "P", using "=MATCH("P",B5:G5,0)"

I'm unsure how to bring them together to get the desired output.

Thanks for any help.

enter image description here

Upvotes: 1

Views: 740

Answers (2)

VBasic2008
VBasic2008

Reputation: 54983

Nested INDEX/MATCH

enter image description here

=IFNA(INDEX($B$1:$G$1,1,MATCH($J2,INDEX($B$2:$G$3,MATCH($I2,$A$2:$A$3,0),0),0)),"")

Upvotes: 1

JSmart523
JSmart523

Reputation: 2497

Insert a new column H where H4 has a formula of =INDEX(B$3:G$3,MATCH("P",B4:G4,0).

Then your second table's second column formula can be =INDEX(H$4:H$5, MATCH(yourDateCellHere, A$4:A$5,0)) because a lot of the heavy lifting is already done in the first table.

Then, if you wish, hide your new column H.

Upvotes: 0

Related Questions