excelexcel-formula

Excel vlookup with multiple criteria not working

I'm trying to use a vlookup on a table with multiple criteria to find the value for the first of the year. I created a test sample to show what I mean. The lookup_YTD column is where I enter the formula as the second image. It looks like

=VLOOKUP(CONCAT(B2,"|",TEXT(E2,"MM/DD/YYYY")),Table1,3,FALSE)

What I'm trying to do is use the helper column F which should be the unique combo of "account" and "date" and make it a key. My vlookup should be searching for that unique key. So in the 2nd row, it should be searching for the account "Stocks", with a separator of "|", and the text version of the date that represents the first of the year "1/15/2025". So I expected G2 to search for "Stocks|01/15/2025" and pull the value $22025.00 from column C. If it helps the formula for the helper column is:

=CONCAT(B2,"|",TEXT(A2,"MM/DD/YYYY"))

formula

sample data

Solution

  • Perhaps try using XLOOKUP - you shouldn't need a helper column, e.g.

    =XLOOKUP(1,(A:A=E2)*(B:B=B2),C:C)