0

what i want is for cell C10 to contain the value of column 5 in my table, only if column 1 and 2 both match the date and name in cells a1 and b1

ive tried to achieve this with a formula but i can only get it to compare 2 things, formula below.

i can get it to match the date in the sheet with the date in the table, then give me column 5's value for that matching row, but i cant get it to acknowledge the name as well so cell C10 is getting a note for someone else because the dates match despite the names not matching.

i have also included a mockup up with 4 examples of what should be happening.

I think it is possible it can be done by formula, i found something when i was trying to google this problem but i dont have a clue what its talking about and i feel like it would be really easy with vba

https://exceljet.net/formula/index-and-match-with-multiple-criteria

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

enter image description here

Improve this question

1 Answer 1

Reset to default
3

you may want to use Database formulas

  1. insert a row above the first one
  2. write "Date" and "Name" in cells A1 and B1 respectively

  3. place the following formula in C10

    =DGET(TblNotes[#All],"Comment",A1:B2)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

hey thanks for giving this a go, i tried what you said and i just get #Value!
wowwwww after some fiddling about and turning a1:b1 into a table i got it working you are an absolute genious!!! i knew it could be done i never even heard of DGET!!. now how do i make it show as blank when there is no match instead of #Value!
=IFERROR(DGET(TblNotes[#All],5,Table6[#All]),"") got it
For some reason that was causing some intense flickering so i started again and worked step by step, and realised you said A1:B2 hehe i thought you said A1:B1, so now it works without turning it into a table and no more flickering, you are the boss!
never know this formula before it is amazing!!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.