CODEWITHSUNDEEP
excelexcel-formuladax
Istiak Mahmood
Istiak Mahmood

Reputation: 2422

Fetch value based on Filter values

I am working on an Excel file where This is my First Table.

enter image description here

Now I have another table that has exactly the same Column names

enter image description here

Now What I wanted is When the Value of Table1[Status] is Active or Pending it should fetch all the values in Table 2. and Don't do anything when the Status value is Pending or Empty

The Expected Output

enter image description here

I have tried the following query but I think I am doing it wrong.

=XLOOKUP([@GID];Table1[GID];SWITCH([@[Status]];"Active";Table1[Status];"Pending"))

Does anyone know how to solve this challenge?

Upvotes: 0

Views: 70

Answers (2)

Terry W
Terry W

Reputation: 3257

Solution 1

A formula based solution cannot display the output in a Table.

Suppose your first table is called Tbl_Source, enter the following formula (in Excel 365) in a single cell and you will get the desired output:

=FILTER(Tbl_Source,(Tbl_Source[Status]="Active")+(Tbl_Source[Status]="Pending"))

formula based solution

Solution 2

If you want to show the filtered output in a new Table, you can try using the Power Query function which is built into Excel 2013 and all later versions.

Firstly, highlight any cell within the first table, then load the table to the Power Query editor:

PQ Step1

Secondly, click the filter button on top of the Status column, and select the desired criteria as you would normally do in Excel:

PQ Step2

Lastly, Close and load the output table. By default, Excel will load the output to a new table on a new worksheet. You can cut and paste the output table to the desired worksheet, or Google how to load the output to a query and then choose where exactly to load the output to.

If you want to add additional data or make changes to the first table, you need to right-click any cell within the output table and choose Refresh, then the output table will be updated with additional data.

PQ Step 3

Let me know if you have any questions.

Upvotes: 1

jprzd
jprzd

Reputation: 481

First

First

Then (Choose Table)

Then

EVALUATE
CALCULATETABLE(
Table1, 
Table1[Status] ="Active" || Table1[Status] = "Pending"
)

Upvotes: 1

Related Questions