Filter a table using value selected in a list and filtering another table using the filtered values

Copper Contributor

Hi All,

 

I have the following tables

 

Table 1

TenderIDProjectTitleOrganisation
2024_1Title 1Organisation1
2024_2Title 2Organisation2
2024_3Title 3Organisation3
2024_4Title 4Organisation4

 

Table 2

TenderIDTataSailJSPL
2024_1YesNoYes
2024_2NoYesYes
2024_3YesYesYes
2024_4YesNoYes

 

I want to select one of Tata, Sail, JSPL from a List

anujkhator_1-1718975680735.png

1. Basis the value selected, I want all those TenderIDs where value in Tata column is "Yes"

2. The ultimate objective is to filter Table1 to show the values with above TenderIDs where value of option selected from the list is "Yes"

3 Replies

@anujkhator 

Hello, I have solved your task with FILTER() within FILTER(). There is probably another way.

=FILTER(Table1,ISNUMBER(XMATCH(Table1[TenderID],FILTER(Table2[TenderID],OFFSET(E11,1,XMATCH(J11,Table2[#Headers])-1,4)="Yes"))))

 

Please also see my attached file.

@anujkhator That could be:

 

=FILTER(Table1, ISNUMBER(XMATCH(Table1[TenderID], FILTER(Table2[TenderID], XLOOKUP(B1, Table2[#Headers], Table2) = "Yes"))))

 

See attached, if needed...