Simple Method to extract dynamic list based on criteria in Excel

In this article we will discuss how to extract dynamic list based on criteria using function in excel. Lets dive right in

Extracting Records from a List Dynamically with Functions –

The # One Task in Excel

Hello everybody,

In this post we are going to introduce to you a very important and common task used in Excel which is Extracting Records, from a data table, with 2 Conditions. Please see the pictures/gifs below with a step by step of a real example with the application of simple functions such as SUM, MAX, VLOOKUP, ROWS, COLUMNS, AND and IF.

In the file, that you can download here (Link) we are going to extract, from the table with all sales information, the sales made by any representative at any region, which are our conditions, in another table to make easier to see and work with it:

As we are not using VBA in the post, we need to learn how to extract dynamically the information wanted by changing our conditions using several different functions. First, we need to create 3 columns that will help us during the process – We’ll name it as Logic 1, Logic 2 and x:

Now in column A (Logic 1) we will use a conditional function to match the information we want with the function AND – The result of the function will be FALSE or TRUE depending if the information is the same or not the same.
1. Type =AND(E2=$K$2,F2=$L$2) in cell A2;

1. Press ENTER;

1. Click and drag to A351;

Now we will try to convert the TRUES and FALSES to 1s and 0s in column B (Logic 2):
1. Type =B1+AND(E2=$K$2,F2=$L$2) in cell B2;

1. Press ENTER;

1. Click and drag to B351;

The result that we got is not what we are looking for, so we are going to fix it by using function SUM in Colum C (x):
1. Type =SUM(C1,AND(E2=$K$2,F2=$L$2)) in cell C2;

1. Press ENTER;

1. Click and drag to C351;

Now we are going to use the MAX function to count how many sales were made considering our two conditions (Representative and region):
1. Type =MAX(C2:C351) in cell L4;

1. Press ENTER – If you change the representatives or the regions, the count will change as well;

And now we can start to use the VLOOKUP function to extract the data from our main sales table to the 2 conditions sales table – The VLOOKUP function will search for a number, which indicates what we want to extract, in Column C and will extract the numbers sequentially (First 1, then 2, then 3 and so on). Also, the VLOOKUP will extract only the first occurrence and the following columns to our 2 conditions sales table.

The VLOOKUP function is composed by 4 arguments which are lookup_value (The value we are looking for), table array (Where do we want to find that value), col_index_numer (From where, in the table, the data will be extracted) and range_lookup (Which is if we want a approximate or exact match):

1. Type =VLOOKUP(1,$C$2:$I$351,2,FALSE) in cell N2;

- 1. Press ENTER;

- 1. Click and drag the formula to the columns and rows available using Fill Without Formatting and you will see that it’s not going to work;

- 1. To fix that we will have to use the COLUMNS function, which increments as you drag horizontally, as the argument col_index_numer, because with the that we can extract the numbers sequentially in the columns – You’ll have to lock (Pressing F4) the first select column to make it a absolute referece, not changing, while the second one selected, will;

- 1. And to fix that we will have to use the ROWS function, which increments as you drag vertically, as the argument lookup_value, because with the that we can extract the data sequentially in the rows – You’ll have to lock (Pressing F4) the first select row to make it a absolute referece, not changing, while the second one selected, will;

1. So, our new formula will look like this – Type =VLOOKUP(ROWS($U$1:U1),$C$2:$I$351,COLUMNS($U$1:V1),FALSE) in cell N2;

1. Press ENTER;

1. Click and drag the formula to the columns available using Fill Without Formatting;

To see all the information about the sales from our 2 conditions, depeding on their Count, we need to drag the formula downwe in the rows, however a error is going to appear if we drag the formula in more rows than the number of sales made select by our conditions:

1. To fix it, we will use the IF function and copy our, already made, VLOOKP function in side of it – Copy the VLOOKP with CTRL+C;

1. Type =IF(ROWS($U$1:U1)> $L$4,””,VLOOKUP(ROWS($U$1:U1),$C$2:$I$351,COLUMNS($U$1:V1),FALSE)) in cell N2 – Our VLOOKUP was used as the argument [valeu_if_false] inside the IF function;

1. Press ENTER;

1. Click and drag the formula to the columns available using Fill Without Formatting;

1. Now, when we drag the formula down in the rows, after the number of sales is reached, the function stop working giving us the perfect table:

(EXTRA) If you want you can hide the support columns A, B & C so them don’t stay visible during the usage of the project:
1. Select all three columns by clicking in column A and draging until column C – An black down arrow will appear;