Formular to search through table

[FelixExcel]

Hey all,

I have a table that has one column of dates, one column of days and one column of calender weeks.

For example:

Date Day Week
30.10.22 Monday 40
31.11.22 Tuesday 40
01.10.22 Wednesday 40
30.11.23 Friday 44

I would like excel to find the date if I give it the week and the day. For example, if I write 40 in cell A1 and Monday in Cell A2 I want excel to search through my table and give me back the date that is in the same row as 40 and Monday, so 30.10.22.

I'm sure there is a formula to do this, but I just can't get it to work.

Any help is appreciated.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[FelixExcel]

Hey all,

I have a table that has one column of dates, one column of days and one column of calender weeks.

For example:

Date Day Week
30.10.22 Monday 40
31.11.22 Tuesday 40
01.10.22 Wednesday 40
30.11.23 Friday 44

I would like excel to find the date if I give it the week and the day. For example, if I write 40 in cell A1 and Monday in Cell A2 I want excel to search through my table and give me back the date that is in the same row as 40 and Monday, so 30.10.22.

I'm sure there is a formula to do this, but I just can't get it to work.

Any help is appreciated.

Just to make it clear, the table has a lot of dates, so each weekday and calender week appear more then once in the table. But the combination of a specific day and a specific calender week only exists once in the table.

 

[Peter_SSs]

the combination of a specific day and a specific calender week only exists once in the table.

In that case, and since you have updated your version (thanks for that), try

22 09 30.xlsm
	A	B	C	D	E	F	G
1	Date	Day	Week			Week	40
2	30.10.22	Monday	40			Day	Tuesday
3	31.11.22	Tuesday	40			Date	31.11.22
4	01.10.22	Wednesday	40
5	30.11.23	Friday	44
Sheet2 (2)

Cell Formulas
Range	Formula
G3	G3	=FILTER(A2:A5,(C2:C5=G1)*(B2:B5=G2))

 

[Automatrix]

Alternatively:

=XLOOKUP(G1&G2,C2:C5&B2:B5,A2:A5)

 

[FelixExcel]

In that case, and since you have updated your version (thanks for that), try

22 09 30.xlsm
	A	B	C	D	E	F	G
1	Date	Day	Week			Week	40
2	30.10.22	Monday	40			Day	Tuesday
3	31.11.22	Tuesday	40			Date	31.11.22
4	01.10.22	Wednesday	40
5	30.11.23	Friday	44
Sheet2 (2)

Cell Formulas
Range	Formula
G3	G3	=FILTER(A2:A5,(C2:C5=G1)*(B2:B5=G2))

Thank you so much. It works perfect. I tried to find a solution with index match because I didnt know about the filter function. Happy to have learned something new today

 

[Peter_SSs]

You're welcome.

I tried to find a solution with index match

You could use INDEX/MATCH (or XLOOKUP as suggested above) but if doing that I would generally recommend using a delimiter to avoid possible errors due to false matches. That would not happen with this particular data anyway, but a good safety measure habit.

22 10 01.xlsm
	A	B	C	D	E	F	G
1	Date	Day	Week			Week	40
2	30.10.22	Monday	40			Day	Tuesday
3	31.11.22	Tuesday	40			Date	31.11.22
4	01.10.22	Wednesday	40				31.11.22
5	30.11.23	Friday	44
Match

Cell Formulas
Range	Formula
G3	G3	=INDEX(A2:A5,MATCH(G1&"|"&G2,C2:C5&"|"&B2:B5,0))
G4	G4	=XLOOKUP(G1&"|"&G2,C2:C5&"|"&B2:B5,A2:A5)

Here is an example of how errors can occur without the delimiter. We can see by looking that the expected result is Name 3, but without the delimiters, we get a different result.
There is nothing special about "|" as the delimiter, it just needs to be something that will not already occur in the data.

22 10 01.xlsm
	A	B	C	D	E	F	G	H
1	Name	Value 1	Value 2			Value 2	20T
2	Name 1	T2	20			Value 1	2
3	Name 2	4	30X
4	Name 3	2	20T				With delimiters	Without
5	Name 4	T5	30			Name	Name 3	Name 1
6							Name 3	Name 1
Match (2)

Cell Formulas
Range	Formula
G5	G5	=INDEX(A2:A5,MATCH(G1&"|"&G2,C2:C5&"|"&B2:B5,0))
H5	H5	=INDEX(A2:A5,MATCH(G1&G2,C2:C5&B2:B5,0))
G6	G6	=XLOOKUP(G1&"|"&G2,C2:C5&"|"&B2:B5,A2:A5)
H6	H6	=XLOOKUP(G1&G2,C2:C5&B2:B5,A2:A5)