Google Sheets Query IF column problem Converted to Excel Function?

In G2 of the attached file I entered this non-array formula and filled down to G7:

Formula:

Please Login or Register  to view this content.

Then in F2 entered this array formula (must be committed with Ctrl + Shift + Enter) and filled down to F7:

Formula:

Please Login or Register  to view this content.

Does this do what you want?

It appears to be pretty close. The results seem to output the desired information. The only thing is that the data resulted in F6 and F7 shows individuals not scheduled to work. With google, if the person doesn't have a value of 7,9,10 then it doesn't look to the coresponding column or present data.

Seems like you've essentially used two forumulas to present the data. The first one, I'm trying to understand what it's doing.

The first part, "=IFERROR(SMALL($B$2:$B$7,ROWS($1:1)),"")"

Looks like it's looking in B2 to B7, but I'm not sure what SMALL and ROWs and the 1:1 represent? Looks like it's findinging all the values in the defined area and returning them in order, not sure what the IFERROR is?

=INDEX($A$2:$A$7,SMALL(IF(G2=$B$2:$B$7,ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1),COUNTIF($G$2:G2,G2)))

So, "=INDEX($A$2:$A$7" this part defines the area that is being indexed?

I'm having a hard time understanding what the rest of it does, or how it interacts with what was put in G2?

From Post 1:
Essentially I query the whole schedule then select column A and B, A for the name and B day of the week where the value in column B = 7,9,10 the shift they work and then create the list in order by B. The Google gives me this but can't find a way in excel.

From Post 4:
Looks like it's looking in B2 to B7, but I'm not sure what SMALL and ROWs and the 1:1 represent? Looks like it's findinging all the values in the defined area and returning them in order, not sure what the IFERROR is?

Column G sorts the list in order (7, 9, 10). It uses the SMALL function and sorts using the ROWS function to designate which smallest to list next. ROWS reports a row count as you copy the formula down yielding the 1st, 2nd, 3rd.......smallest numbers in the list. The IFERROR((<formula>),"") part suppresses errors that occur because the formula is entered over 6 rows (because there are 6 individuals in column A) with only 4 values (from column A) to return. Without the IFERROR the results would look like this:

Row\Col	E	F	G	H	I
1		Name	Monday	Tuesday
2		Randy	7
3		Jim	9
4		James	9
5		Bob	10
6		#NUM!	#NUM!
7		#NUM!	#NUM!
8

From Post 4:

So, "=INDEX($A$2:$A$7" this part defines the area that is being indexed?

Yes, and associates those names with their respective times (listed in sort order) and returns them in column F.

This is the way I interpreted your request. Did I misinterpret?

Edit: Would you like a reworked set of formulas that filter out the individuals not working?

Originally Posted by Garthilk

Yes please.........

The attached filters out those individuals.

Greetings playmate and welcome to the Forum.

As a newcomer you may not be aware of some Forum rules. It will help you to resolve your question sooner. Please acquaint yourself here:

http://www.excelforum.com/forum-rule...rum-rules.html

One would be to not post your question in the thread of another. You need to start your own thread.

Another is simply good advice and that would be to post an actual Excel spreadsheet file in the Forum and not pics.
Many of us cannot read them (me for example) and it saves having to retype your data. Also please post directly to the
Forum. Posting to third party sites makes some of us nervous (me for example ) about clicking on unknown links.

Thanks,

Looking forward to your new thread.

P.S. If you are unfamiliar with how to start a new thread click on any of the sub-Forums. You'll see a "Post New Thread" button in the upper-left corner.