Index, Match and large formulas together

I'm not sure if you just want the name and hours in separate cells, or a combination of name and hours worked in one cell, for each column.

If you want them in separate cells, put these two formula above/below your table, in column F (e.g. F19 and F20) and drag across to column J:

Formula:

Please Login or Register  to view this content.

You can add text using &, like this:

Formula:

Please Login or Register  to view this content.

If you want the name and hours together in one cell, use this, dragged across:

Formula:

Please Login or Register  to view this content.

You'll need to Wrap Text (Home tab, Alignment section) to see the results in full.


Does one of those do what you want?

I'm guessing you're applying them to a real file, not the sample file? So it's probably because the ranges aren't quite right.

I'll go through the longest formula, explaining what each bit does, so you can understand what you might need to change.

Formula:

Please Login or Register  to view this content.

First, let's split out the different parts which have been joined together. The overall formula consists of two smaller formulae (the ones listed separately in my previous post), along with some text in between.

Formula:

Please Login or Register  to view this content.

The & symbol tells Excel to join two things together.
For example, if you have a formula which adds two cells (like =A1+A2), you could put 'The total is' before the result like this: ="The total is " & A1+A2
This would give you a result like this (let's assume that A1 is 10 and A2 is 5): The total is 15
Note that the text is enclosed in quotation marks, including a space so that you get a space before the number. I've also put spaces around the & sign, but that's just for clarity; it could be compressed to: ="The total is "&A1+A2

So, the formula I gave you uses these & signs to give you:

Formula:

Please Login or Register  to view this content.

Large
Let's look at the Large formula first, as it's embedded in the Index-Match one, so it's easiest to explain this way round.
The format of the Large function is:

Formula:

Please Login or Register  to view this content.

range = the range you want to find the largest value in
k = a number which says that you want the k-largest number

So if you had 8,11,14,21 in A1:A4, then you could use Large like this:

LARGE ( A1:A4 , 1) would give 21 - the largest number
LARGE ( A1:A4 , 3) would give 11 - the third-largest number

Therefore, in the Large formula I gave you, the function looks at the range F$4:F$17 and finds the largest number in it. For column F, this is 72. For col G, it's 14.

(I'm going to assume you understand what the $ symbol means - if not, have a look at this page: https://support.office.com/en-gb/art...e-5f0d8d0baca9)


Index-Match
As you'd expect, this is made up of two functions: Index and Match
The format of the Index formula is:

Formula:

Please Login or Register  to view this content.

array = the range from which you want to get a result
row number = the row that result is in
column number = the column that result is in

The most common use of Index looks at just one column, so the column number isn't needed and can be missed out - so giving just INDEX ( array , row no ). The row number is often found by using the Match function, which has this format:

Formula:

Please Login or Register  to view this content.

lookup value = the value you want to find in the lookup array
lookup array = the range where you want to find the value
match type = normally 0, to specify an exact match

So, if you have the same numbers in A1:A4 as before (8,11,14,21) and you use this formula:

=MATCH ( 14 , A1:A4 , 0 )

you will get a result of 3, because the value 14 is in the third row of the array. Note that it's the row of the array which is returned, not the row number visible in Excel - if the numbers were in A10:A13, you'd still get the same result of 3.

So, since Match gives us a row number, we can use it inside the Index function:

Formula:

Please Login or Register  to view this content.

Going back to the Index-Match formula I gave you, this is how it breaks down:

Formula:

Please Login or Register  to view this content.

$C$4:$C$17 & " " & $D$4:$D$17 = the range you want as a result - it's a combination (using &) of columns C and D (first and last names)
MATCH ( LARGE ( F$4:F$17 , 1 ) , F$4:F$17 , 0 )
F$4:F$17 = the range from which you need to get the row number of a value - the value you want to look up is the largest number in that range, which is where the Large function comes in - as explained earlier.

So, in words, the formula is:

Formula:

Please Login or Register  to view this content.

That's a horribly long and convoluted explanation, sorry. I've also attached your file, with all the formulae in it, a bit colour-coded to try to show what refers to what.
I hope that's of some help. If it's still not working, please let us know.

You're welcome, glad I could help. Thanks for the rep and marking this as Solved.