Inputting several values, found from a VLOOKUP function, into a COUNTIF function

Question

This question is about a single function (in a single cell to later extrapolate over a whole table) in Microsoft Excel.

I want to find a unique id on another page (amongst other id's in the first column) (using the VLOOKUP function, I think) and compare the values in the other columns (in the same row) to a particular value in this page.

Basically, the first page is a few (about 6, some cells blank) dates for each unique id, the ids are in the first column, each's corresponding dates are in the same row in certain other columns (3rd, 5th, 7th, etc.). The second page is the first column as id's, and the upcoming dates (over the upcoming week) along the top row. Obviously, if a unique id (row) has the column's date (in any of the specified cells adjacent to that same id in the 1st sheet) then the cell has a 1 (or true or whatever) otherwise it has a 0 (or false, etc.). Then I plan to order the columns with the nearest upcoming ids at the top.

For the second part about checking if they match the 2nd sheet's column's date, I can only think of using the COUNTIF function, but that's only useful for looking at several cells whereas having this all in a single function seems to be inputting in as a list.

Also, the second part is comparing dates and when I'm building up a function to try to get to this end function, I often find value error which I'm confused about, I've tried changing the list and the criteria to and from: text, serial numbers, dates.

This is the function I've used for a single column of dates on sheet 1:

=IF((VLOOKUP($A5,Sheet1!$A$2:$C$6,3,FALSE)=C$1),1,0)

This is the function I used to make the values found by VLOOKUP into a list to try to input it into a COUNTIF function:

=("{"&TEXTJOIN(",",TRUE,(VLOOKUP($A3,Sheet1!$A$2:$L$6,{3,5,7,9,11},FALSE)))&"}")

And this is the most recent overall attempt:

=IF(((COUNTIF((“{“&TEXTJOIN(“,”,TRUE,VLOOKUP($A3,Sheet1!$A$2:$L$6,{3,5,7,9,11},FALSE)))&“}”),B$1)>0)=TRUE),1,0)

here is an example of sheet 1

here is an example of sheet 2

Answer 1

I copied your source sheet into my Sheet1. It looked like this:

If I've understood correctly, you want to have a grid of TRUE/FALSE or 1/0 whose row headers are the unique ID and whose column headers are a sequence of dates.

I created a sheet like this:

To populate the TRUE/FALSE values, we need to determine a few things:

1. The row in the source data we want to look in
2. If the date in the column header is in the list of dates in the row found. Actually, the position of the date in the row doesn't really matter.

After some experimentation with INDEX, MATCH, INDIRECT and so on, it started getting messy. This does what you need:

=NOT(ISERROR(INDEX(Sheet1!$A$1:$F$10,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,INDIRECT("Sheet1!$A$"&MATCH($A2,Sheet1!$A:$A,0)&":$F$"&MATCH($A2,Sheet1!$A:$A,0)),0))))

I wasn't satisfied with that, mostly because I don't like string concatenation in formulas. When something looks too complex, it probably is. It turns out the same result can be achieved with this:

=SUM(IFERROR(FIND(B$1,XLOOKUP($A2,Sheet1!$A$2:$A$10,Sheet1!$B$2:$F$10)),0))

That formula gives this result:

Let's break down why it works.

XLookup and Spill formulas make this much easier. If I just use XLookup, I can return the whole row for the Unique ID.

So, I can get all the dates for "alpha":

=XLOOKUP($A2,Sheet1!$A$2:$A$10,Sheet1!$B$2:$F$10)

That's great. Next, I need to look in those dates to see if I can find the column header (April 15). FIND will do that.

=FIND(B$1,XLOOKUP($A2,Sheet1!$A$2:$A$10,Sheet1!$B$2:$F$10))

Ok, so now I have a 1 and some errors. I can turn the errors into zeroes by using IFERROR:

=IFERROR(FIND(B$1,XLOOKUP($A2,Sheet1!$A$2:$A$10,Sheet1!$B$2:$F$10)),0)

I just want a single result, so if I wrap the whole thing in SUM, it will show a 1 if it's found the date in the column header, otherwise a 0.

=SUM(IFERROR(FIND(B$1,XLOOKUP($A2,Sheet1!$A$2:$A$10,Sheet1!$B$2:$F$10)),0))

It's not too much more effort to convert the 1s into TRUE and the 0s into FALSE (if you prefer that) and note that if the date-header is found more than once in the row on the source sheet, the number returned will be >1, but perhaps that's useful in some other way.