Value lookup formula

[slopez94]

Hello, Everyone

I am needed some help with what I think would be a v look up formula. On one worksheet I have a refreshable report that pulls information, this report lists many rows of info for the same service order number (column A), on another worksheet I would like to pull in all the rows that match with the service order number identified in cell B2 (in the example below, 6247).

What would be the best way to pull in this data from one worksheet to the other?

 

[Joe4]

Since you are using Excel 365, you are in luck! This is very easy to do with the new FILTER function.

See here for details and examples:

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

 

[slopez94]

Since you are using Excel 365, you are in luck! This is very easy to do with the new FILTER function.

See here for details and examples:

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

I will be using the second worksheet for various service order numbers; I will also be modifying this worksheet with added columns to pull in data from other workbooks. For this reason, I rather pull the data I'm looking for from the original refreshable report rather than just filtering it.

 

[Joe4]

Do not confuse the new FILTER function with the old Filtering feature - they are two different things entirely!
The FILTER function is new, and only available for Excel 2021 and Excel 365.
It is also totally dynamic. It is a Spill function, meaning you just enter the formula in one cell, and all the matches will spill out for however many rows match the criteria (and however many columns you are telling it to return).
You can also reference other cells in the criteria portion of the formula (i.e. if you have the invoice number in cell B2).

Think of it as VLOOKUP on steroids. VLOOKUP can only return one single match (the first one it finds) to one cell.
FILTER can return multiple matches and multiple columns, all in a single formula, and the results can change dynamically as your original data source changes.