CODEWITHSUNDEEP
excel
Chris R
Chris R

Reputation: 65

Index + Match not working as intended, only working for first instance

The formula below is working sort of. its only working for the first date in the table tied to that person.

Cell C10 named "Date" automatically updates with the date when i select a date inside of a range. so if cell B2 has the date 01/01/2020, and i select it, cell C10 will now have the value 01/01/2020. Cell A1 named "Name" contains a name which is changed via a list in that cell.

i want the formula to find the row in my table that has the same name and date as the name and date in my worksheet, then give the cell containing the formula the value of column 5 of the matching row in my table

=IFERROR(IF(MATCH(Date,INDEX(TblNotes[#All],MATCH(Name,TblNotes[[#All],[Name and Surname]],0),3),0),True),False)

Edit: ok so this might help people understand what im trying to say, ive got it to work now matching the date, i now want it to cross reference the name because if the the dates match it is showing me the value i want for the wrong person

=INDEX(TblNotes[#All],MATCH(Date,TblNotes[[#All],[Date]],0),5)

ok here is an example of what i am trying to achieve, and the setup is similar to mine https://i.sstatic.net/hkHoS.jpg

Upvotes: 0

Views: 1676

Answers (1)

David
David

Reputation: 1232

Based on your screenshot, where the date is in A1, the name in B1, and the table in G4:I7, you can use this array formula in C10/anywhere.... note it is an array formula and must be entered using CTRL+SHIFT+Enter:

=INDEX(G4:I7,MATCH(A1&B1,G4:G7&H4:H7,0),MATCH("Comment",G4:I4,0))

It's an array formula because there are two row match criteria in the first match function. Switching in the named ranges / named table, the formula looks like this:

=INDEX(TblNotes[#All],MATCH(Date&Name,TblNotes[[#All],[Date]]&TblNotes[[#All],[Name]],0),MATCH("Comment",TblNotes[#Headers],0))

(I assumed range B1 is named 'Name').

Upvotes: 1

Related Questions