Index Match Lookup Combo

[Literae]

Hi All,

I created a spreadsheet years ago in my previous job which did exactly what I want my new one too. However I have left the company now and dont have access to it to review the formula. Basically I have a sheet with =today() in cell AA1 (for example). I would like the following cells to list all the names (in col A) that have a number greater than 0 (in col B and later col C, D, E etc). I would like it to change based on the date which is listed at the top of the columns. Please see below for reference:

Data:

Name (A1)	01/01/2023 (B1)	01/02/2023 (C1)
Mr A (A2)	5.00
Mr B (A3)	5.00
Mr C	0	2.75
Mr D	0	0

Dashboard:

Date	01/01/2023 (this is cell AA1 - list to change based on this cell)
Holiday
Mr A (this is where I would want my list to start)	5.00 (I would just use lookup to bring through the hours so this cell is fine, unless there is a formula for this too? im pretty sure I had one for each of the 8 columns in my last sheet that did this)
Mr B	5.00 (I would just use lookup to bring through the hours so this cell is fine, unless there is a formula for this too? im pretty sure I had one for each of the 8 columns in my last sheet that did this)

Basically use AA1 as a reference to search B1:Z1. If finds the matching date, then search the column for anything over 0 and bring through the corresponding name in column A.

When the date changes in AA1 to 01/02/2023 for example, it would then reference the date and search column C1 etc.

Hope this makes sense? I have had a hiatus from formulas for about 2 years and had a blank and just cant get it right.

Thanks in advance
Dan

 

[Rows N Columns]

Can you see if this works? Paste this formula in row 2 of the column you want the list of names to be. It will get the name from Column A if the value in the column that corresponds to the date in AA1 is more than 0. Copy this formula down for all rows where you have data.

=IFERROR(INDEX($A$2:$A$12,SMALL(IF(($B$1:$Z$1=$AA$1)*($B$2:$Z$12>0),ROW($B2:$B$12)),ROW(1:1))-1,1),"")

Do take note to hit Ctrl+Shift+Enter to confirm the array formula if you are using an older version of Excel.
Also, I have just selected a range up to row 12, but do update the formula accordingly if your data exceeds 12 rows.

 

[Literae]

Hi Rows,

Thats on the right track thank you! Just need to tweak a bit my side. However when I try and add Sickness into the mix (changing >0 to "S" picks up Sickness, but still records anything that isnt blank. For example when I change the date to 12/08/2023, my sickness picks up Mr F correctly, but holiday also picks up Mr F along with Mr K. It also picks up the blank cells (contains "X") when I change to 13/08/2023 etc.

Thanks for your help so far :D

 

[Rows N Columns]

Is it possible for you to upload the image using XL2BB so that I can use your data without having to recreate it from scratch? Also, may I know what the expected output should be for 12/8/2023 if the formula was correct? Not too sure what you mean by "your sickness picks up..." and "holiday picks up..".

 

[Fluff]

@Literae whilst you have posted this question in the right section (thanks for that) you need to make it clear what app you are using, otherwise people will assume it's Excel.

 

[Literae]

Oh apologies all, its Google Sheets sorry.

Hope ive done this XL2BB right:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Name	01/08/2023	02/08/2023	03/08/2023	04/08/2023	05/08/2023	06/08/2023	07/08/2023	08/08/2023	09/08/2023	10/08/2023	11/08/2023	12/08/2023	13/08/2023	14/08/2023	15/08/2023	16/08/2023	17/08/2023	18/08/2023	19/08/2023	20/08/2023	21/08/2023	22/08/2023	23/08/2023	24/08/2023	25/08/2023	01/08/2023
2	Mr A
3	Mr B
4	Mr C
5	Mr D
6	Mr E															4.00
7	Mr F										S	S	S	S
8	Mr G	8.00				8.00
9	Mr H	8.00					X							X							X
10	Mr I	8.00
11	Mr J			X			X				X			X				X	6.00	6.00	X
12	Mr K	8.00											8.00
13	HOLIDAY
14	Mr G
15
16
17
18
19
20
21	SICKNESS
22
23
24
25
26
27
28
29
Sheet1

Cell Formulas
Range	Formula
A14	A14	=IFERROR(INDEX($A$2:$A$12,SMALL(IF(($B$1:$Z$1=$AA$1)*($B$2:$Z$12>0),ROW($B2:$B$12)),ROW(1:1))-1,1),"")
A15:A20	A15	=IFERROR(INDEX($A$2:$A$12,SMALL(IF(($B$1:$Z$1=$AA$1)*($B$2:$Z$12>0),ROW($B4:$B$12)),ROW(3:3))-1,1),"")
A22:A23	A22	=IFERROR(INDEX($A$2:$A$12,SMALL(IF(($B$1:$Z$1=$AA$1)*($B$2:$Z$12="S"),ROW($B11:$B$12)),ROW(10:10))-1,1),"")
A24:A29	A24	=IFERROR(INDEX($A$2:$A$12,SMALL(IF(($B$1:$Z$1=$AA$1)*($B$2:$Z$12="S"),ROW($B$12:$B13)),ROW(12:12))-1,1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Regarding the Holiday / Sickness table, I wanted the holiday cells to bring across anything over 0 so the number of hours holiday etc and the Sickness to bring across anything with S in. When I changed it, it recorded the S, but also brought across the Sick person into Holiday too (and anything else that wasnt blank).

Thank you in advance

 

[Rows N Columns]

Formula for A14:

=IFERROR(INDEX($A$2:$A$12,SMALL(IF(($B$1:$Z$1=$AA$1)*(ISNUMBER($B$2:$Z$12))*($B$2:$Z$12>0),ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

Somehow Excel sees text as more than 0, so I had to add in the ISNUMBER function to also check that the value is a number.

Formula for A22:

=IFERROR(INDEX($A$2:$A$12,SMALL(IF(($B$1:$Z$1=$AA$1)*($B$2:$Z$12="S"),ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

Let me know if it's ok now

 

[Literae]

Omg thank you so much! That works perfectly thank you. Also learned about XL2BB today so that will also help in getting my problems across in future.

Thank you so much Rows!

 

[Rows N Columns]

You're very welcome

 

[Literae]

....so.....I moved my main data to a new tab (Dashboard Data) and have the Holiday/Sickness on a seperate tab (Dashboard). I have adjusted all the cell references to look in the other tab (Dashboard Data), but the 1:1 still references Dashboard. What does the 1:1 and -1:1 do and should I amend them to something?