A question about Microsoft Excel?

Question

This is something similar to HLOOKUP and VLOOKU, the trouble is my values aren't in any order so LOOKUP would return the first of possibly several values that match the criteria.

For example: Sheet 1 has 3 columns - A is Date, B is Total 1 and C is Total 2. The date column is sequential, the total columns are random numbers. On sheet 2 I use the Large function to return the largest value from a range within the Total columns (B1:C10 for example). How would I return a value from the same row but a different column (e.g. if the largest value in B1:C10 was C6, how would I return A6).

Answer 1

This will do it.

=INDEX(A1:A10, MAX(IF(B1:C10=MAX(B1:C10), ROW(B1:C10),0)) - ROW(A1)+1)

Input this using Ctrl-Shift-Enter (instead of just Enter), since it's an array formula.

- A1:A10 is the "different column" that you want to return the value from.
- A1 is the first cell in that column
- B1:C10 is the range where you find the MAX (you can use the LARGE function instead if you wish)

If you need more info, add more details or e-mail.
Good luck.
.

Answer 2

Okay, I'm sure there is an easier way to do this, and if I find it I'll tell you. But off of the top of my head, this will work, although it's messy:

Given that A1:A100 contains the data you want to pull out, and B1:B100 is the data that you want to search to find the largest, create column C that contains the data in A if the data in B is the largest, and the data in the cell above it if the data in B isn't the largest. C1 would contain:

=if(B1=LARGE(B1:B100,1),A1,"")

And C2 through C100 would contain:

=if(B1=LARGE(B1:B100,1),A1,C1)

When you fill down from C2 to the bottom, you'll find that the last cell contains the value from column A where B is the largest. Of course, you have a bunch of other crap in all of the other C column cells, but the last cell is the value you want. :-)

Answer 3

Lemme guess, you're in a working laptop or laptop Literacy classification... possibly with the artwork Institute??? by using fact whats humorous is it somewhat is the top question of this weeks first task. extraordinary. "Week 3: Week 3 - W3: task one million talk talk question 3 evaluate Microsoft be conscious, Microsoft Excel, and Microsoft PowerPoint. while would desire to you utilize each and each of those purposes? while are you able to apply those purposes interchangeably? provide examples and motives to assist your answer."

Answer 4

Someday, after I've taken a computer class for my job, I'll know what you're talking about.
;-)