Grabbing values from one table based on a certain condition to make another table.

**Adrian.L** · 01-10-2016, 08:17 AM

Hi all,

Firstly I apologise if this is a duplicate question. I could not figure out the proper terminology to begin searching what I needed.

I would like to create a new table using a set criteria that would grab data from another table. Is it possible?

Here is an example:

Excelhelp.JPG

The goal is to enter a value into cell B11 and then have the area below row 13 filled in with the names of people who match that criteria.

Example if i were to enter "75" into cell B11, below row 14 would display:

Tom 60
Jill 35
Sam 65
John 70

Thank you for the help!

Adrian Lee

**Tony Valko** · 01-10-2016, 08:46 AM

Try this...

Data Range

	A	B	C	D	E
1	Name	Mark		Mark	75
2	Betty Diddent	34
3	Anne Teak	57		Betty Diddent	34
4	Robin Banks	83		Anne Teak	57
5	Barb Dwyer	76		Ben Dover	64
6	Dusty Rhodes	82		Warren Peece	27
7	Ben Dover	64		Kerry Oki	23
8	Warren Peece	27
9	Justin Thyme	95
10	Kerry Oki	23
11	Rusty Steele	81
12	------	------	------	------	------

This array formula** entered in D3:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$11<E$1,ROW(B$2:B$11)),ROWS(D$3:D3))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula entered in E3:

=IF(D3="","",VLOOKUP(D3,A$2:B$11,2,0))

Select D3:E3 and copy down until you get blanks.

**Adrian.L** · 01-10-2016, 10:17 AM

Great! It works. Thanks a lot.
However I would like to add on.
What if columns "A" and "B" were not side by side?
What if there was a column in between but I still desired the same outcome.
I understand the first portion would still work fine, but it seems to affect the second portion. I'm assuming it has to do with the "A$2:B$11" part. It's not as simple as just changing "B" to "C" is it?
Is there anyway around it?

**Tony Valko** · 01-10-2016, 11:17 AM

Tell us EXACTLY where the data is located.

Better yet, post a SMALL sample file and show us what results you expect.

20 rows worth of data is plenty.

**Adrian.L** · 01-10-2016, 11:37 AM

I apologise for being vague earlier.

The full story is this:
Excelhelp2.JPG

I've been tracking club member's attendance for a while now, however recently I wanted to come up with an easy way to check for people who go below a certain percentage. Hence my questions, and the want to create the new table in the area for J4 onwards.

I'm all ears. If there's an easier way then I'm happy to learn.

Btw, I hope you're okay with me using the same names in the example.

Thanks again,

Adrian Lee

**Adrian.L** · 01-10-2016, 11:39 AM

----------

**Tony Valko** · 01-10-2016, 12:49 PM

Like this...

Data Range

	A	B	C	D	E	F	G	H	I	J	K
1	Number of classes	5					------	------	------	------	------
2		4-Jan	5-Jan	6-Jan	7-Jan	Jan-16		% Attendance		Attendance less than
3	Name:									60
4	Betty Diddent	1	1	1	1	1		100		Dusty Rhodes	40
5	Anne Teak	1	1		1	1		80		Warren Peece	20
6	Robin Banks	1		1	1			60		Kerry Oki	0
7	Barb Dwyer		1	1	1	1		80		Rusty Steele	40
8	Dusty Rhodes			1	1			40
9	Ben Dover	1	1		1			60
10	Warren Peece	1						20
11	Justin Thyme	1		1	1			60
12	Kerry Oki							0
13	Rusty Steele		1	1				40

This array formula** entered in J4:

=IFERROR(INDEX(A:A,SMALL(IF(H$4:H$13<J$3,ROW(H$4:H$13)),ROWS(J$4:J4))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula entered in K4:

=IF(J4="","",INDEX(H$4:H$13,MATCH(J4,A$4:A$13,0)))

Select J4:K4 and copy down until you get blanks.

Grabbing values from one table based on a certain condition to make another table.

LinkBack

Thread Tools

Rate This Thread

Display

Grabbing values from one table based on a certain condition to make another table.

Re: Grabbing values from one table based on a certain condition to make another table.

Re: Grabbing values from one table based on a certain condition to make another table.

Re: Grabbing values from one table based on a certain condition to make another table.

Re: Grabbing values from one table based on a certain condition to make another table.

Re: Grabbing values from one table based on a certain condition to make another table.

Re: Grabbing values from one table based on a certain condition to make another table.

Thread Information

Users Browsing this Thread

Similar Threads

Finding a macro that will populate a table from another table based on a columns values

I want to make a new table, based on info on another table with same ref value

[SOLVED] Get set of multiple values from a table based on condition

Pick up values from a table based on 1 condition

[SOLVED] How to populate a table based on the searched values in a pivoted table

How to make Excel look at table 1 and pick a value from table 2 based on condition.

PLEASE DELETE this post

Bookmarks

Bookmarks

Posting Permissions