Click here for important information about Computing.net.

Dell / LATITUDE C610

In MS Excel, I need an equation for a combined vlookup and large functions based on text in another column. The scenario is 100 students with names in column A, "m" or "f" in column B, scores in columns C through G, and Total scores in Column H. I need to know the top 3 "m" and the top 3 "f". I am running Excel 2003.

Thanks for telling what you need. If you'd like some help with that, don't hesitate to

ask.

I am sorry if I came off as demanding, I am just a little frustrated because I have tried for 2 days to come up with an equation. I am a novice to excel, and have done some research to learn about the different ways to include what I am looking for. Mostly from .edu sites that require a bit more understanding of the multitude of functions in excel than I currently have. But as a last resort I am asking here, because I feel I have fried my patience in trying all the different equations that look to fit this situation. So I am asking, although not very eloquently, for help. I have read about 3 or 4 of your posts DerbyDad3 in regards to excel and you seem to be the "ONE" that could help.

re: " as a last resort I am asking here"We're your last resort? I'm hurt! You should always come to us first. ;-)

Actually, Googling around and trying stuff on your own is a great way to learn about Excel.

In fact, some of the "tricks" I've learn via Googling I applied to this solution.

re: "

you seem to be the "ONE" that could help"Just one of the many...but thanks.

OK, as to your question, when you say "

I need to know the top 3 "m" and the top 3 "f"I assume you mean "based on the total score in Column H".I am also assuming that your data resides in A1:H101, with Row 1 being a header row. Adjustments will need to be made if this is not the case.

The first thing I came up with is this array formula. There may be ways to do it with a non-array formula, but this seems to work.

To create an array formula, you must use Ctrl-Shift-Enter, not just Enter, when you place - or edit - the formula in the formula bar. Once you do this, Excel should put { } around the formula to indicate that it is an array formula. You may get a result without using Ctrl-Shift-Enter, but it may not be the correct result.

This formula, array entered, will look at Column B for those cells that contain "m", determine the highest value from Column H for only those rows, and return the Student's name from Column A.

=OFFSET(H1,MATCH(LARGE(IF(B2:B101="m",H2:H101),1),H2:H101),-7)

How it works:

IF(B2:B101="m",The IF function will create an Array of cells that contain "m" in B2:B101. Change this to "f" for your other criteria.

LARGE(IF(B2:B101="m",H2:H101),1)The LARGE function will return the largest value in the rows within that array from Column H. The "1" means the largest, so change that to 2 or 3 to get the second and third largest values.

MATCH(LARGE(IF(B2:B101="m",H2:H101),1),H2:H101)The MATCH function will determine the position within H2:H101 that the highest value resides. It is not actually a row number, but a relative position from the top of the list.

=OFFSET(H1,MATCH(LARGE(IF(B2:B101="m",H2:H101),1),H2:H101),-7)The OFFSET function will use the value returned by the MATCH function as the

rowsargument. Since we are offsetting from H1, if MATCH returns e.g. "5" as the position in H2:H101 of the largest value, this would offset from H1 5 rows to H6. The -7 tells OFFSET to go back 7 columns from H to A, which in this example, would return the value in A6.Have fun!

The reason you guys were my last hope was I save the BEST for last... no really I didn't hear about this site til this morning, but it has turned out that way. But on to the task at hand. O.k. My data is on A15:H114, so MY equation changed to:

=OFFSET(H14,MATCH(LARGE(IF(B15:B114="m",H15:H114),1),H15:H114),-7)

The value came out "0", so I broke it down step by step:

=IF(B15:B114="m",H15:H114)

returned the value of last number in the H column with "m" in the B column, because the last value overwrites all previous values.

=LARGE(IF(B15:B114="m",H15:H114),1)

returned the value of H15, the largest number in the H column with the "m" in the B column

=MATCH(LARGE(IF(B15:B114="m",H15:H114),1),H15:H114)

returned a value of 100.

This is where I am stumped. According to your explanation and my data this should have been 1 as currently the largest number is in H15 and there is no data on H115. And of course the OFFSET function worked as you said it would, "use the value returned by the MATCH function as the rows argument" and returned the value of A115. B115 does not have a "m" in it so I did not know why the match came up with 100.I then began to look at the MATCH portion of the equation and found it was missing a "match type". In my various searches over the last 2 days one was on arrays and it explained that "false" would give the exact match. So I entered it into the equation and BAM!!! I am cooking with fire now.

My new equation looks like this:

=OFFSET(H14,MATCH(LARGE(IF(B15:B114="m",H15:H114),1),H15:H114,FALSE),-7)

on one line of course. Thank you so much for guiding me in the correct direction!!

I would also assume that to post the scores next to the name, I could use:=LARGE(IF(B15:B114="m",H15:H114),1)

in the next column. THANK YOU VERY MUCH!!! YOU ARE MY HERO.

OH, one more thing. If more than one student has the same score whether B column shows "m" or "f" it will show the first of the duplicate scores in both second and third positions. For example

F G H

First Place "M" A15 H15

Second Place "M" A26 H26

Third Place "M" A26 H26value H26 and H29 are the second largest of H15:H114 and having the "F" in the B column.

I took this one step further. I used the I and J columns for "F" top 3, then gave 6 students the same Highest value in the H column and assigned 3 a "m" and 3 a "f" in the B column. The results:

G H I J

A15 H15 A15 H15

A15 H15 A15 H15

A15 H15 A15 H15Is there anyway I can resolve this duplication? I do remember reading one of your posts to that effect. but I cant remember where to find it. Thanks

re: I then began to look at the MATCH portion of the equation and found it was missing a "match type". In my various searches over the last 2 days one was on arrays and it explained that "false" would give the exact match.If you omit the

match_type, as I did, MATCH will assume it is 1 (TRUE) which will still return an exact match, but thelookup_arraymust sorted in ascending for it to work correctly.Since the values I tested were in ascending order it worked for me, but I can certainly see why a list of students scores

wouldn'tbe ascending order, thus requiring a 0 (FALSE)match_type.Just as an FYI, you can use 1 and 0 to represent TRUE and FALSE in functions such as MATCH, VLOOKUP, etc.

Well a little more research and trial and error has gotten me to where I need to be.

First I solved the crossing of the "m" and "f" criteria by substituting:IF(B15:B114="m",H15:H114)

for the H15:H114 in the MATCH table array. Thus making my equation as follows:

=OFFSET(H14,MATCH(LARGE(IF(B15:B114="m",H15:H114),1),IF(B15:B114="m",H15:H114),-7)

With that being solved i decided to use recommendations on the posts Mr Humar made to maram here:

http://www.computing.net/answers/of...

post #6. I added the string:

,ROW(A15)/100000

to the SUM equations in H15:H114 which changed the scores just enough to offset the duplication. Again many THANKS to all here, you guys offer us noobs a way to learn with hope!!!

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History