# Vlookup highest to lowest and return column name

1. ## Vlookup highest to lowest and return column name

Dear Forum members,

In Sheet 1:
I have a list with customers (column A) with behind it the turnover within different product groups and the total turnover. Example:

excel1.png

In Sheet 2:
I managed to sort the data from sheet 1 to get a "top 4" list with customers based on its total turnover from highest to lowest. Example:

excel2.png

Required:
Now I want to know the 'Top 4' product groups which need to go behind the customers top 4 list. Example:

excel3.png

Hope someone is able to help me solving this issue. Thanks indvance.

2. ## Re: Vlookup highest to lowest and return column name

Your data structure is pretty inefficient... You'd be better storing the data in a normalised form, then reporting it in a crosstab.

However, see the attached for a worked example which does what you want, using the rather nasty formula:
Formula:
`Please Login or Register  to view this content.`

3. ## Re: Vlookup highest to lowest and return column name

I would suggest a helper column for the MATCH(\$C11,\$F\$2:\$F\$5,0) section might be in order.

4. ## Re: Vlookup highest to lowest and return column name

Originally Posted by xlnitwit
I would suggest a helper column for the MATCH(\$C11,\$F\$2:\$F\$5,0) section might be in order.
I wouldn't - I'd suggest storing the original data in a better structure, in the first place!!

5. ## Re: Vlookup highest to lowest and return column name

Indeed but, given the solution you provided, I think the helper column is in order.

I imagine we are all aware that realities of life often interfere with how things actually should be done.

6. ## Re: Vlookup highest to lowest and return column name

Hi Olly,

Thank you for your post. From your solution you are matching the total turnover per customer (....;MATCH(\$C11;..) to get the required data as a result. However it is possible that from all the customers some would have the same (total) turnover. Won't this result in incorrect output? Can I use 'Customer' instead (....;MATCH(\$B11;....) since this is a unique value?

Thanks again.

Update:
I changed the MATCH lookup value to 'Customer' \$B11 and altered the lookup array as well. Seems to work fine.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1