• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Returning the row value in a dynamic dataset

E

Elgohary11

New Member
Hello!

I've find how to find a value with INDEX MATCH MATCH to return a value in a dataset. But I want to reverse this, and find the row that corresponds to the highest value in each column.

upload_2015-3-25_12-9-39.png

For example: I want to be able to find for ACC - that "Website" has the highest value.

Can someone please help! Thank you!
 

Attachments

  • upload_2015-3-25_12-7-31.png
    upload_2015-3-25_12-7-31.png
    36.8 KB · Views: 3
Elgo -- Welcome to the Forum!

In Cell B18, I would enter the following formula:

=INDEX($A$2:$A$12,MATCH(MAX(B$2:B$12),B$2:B$12,0))

Then drag right to fill all the cells in this row.

Is this what you're looking for?
 
Last edited:
Thank you so much!

I am wondering if it is possible if I had another table to fill out, so the formula would read the criteria and return the value.

For example this:
 

Attachments

  • upload_2015-3-25_13-1-45.png
    upload_2015-3-25_13-1-45.png
    12.6 KB · Views: 2
If this new "summary" table is on a separate worksheet, you can paste the following formula in cell B2 of the new table -- and drag it down to autofill:

=HLOOKUP(A2,Sheet1!$B$1:$V$18,18,TRUE)

Before you autofill, make sure that you change the bold text in this formula to match the actual name of the original worksheet.

It wouldn't be too hard to skip the intermediate step (the helper row) that we set up earlier...but I didn't know how you would plan to fetch the information after you tabulated it.

When you have similar questions in the future, if you can post both the "input" table and the "output" table, we may be able to develop a really snazzy formula to make it all happen in one shot.

I've attached a table that mimics yours (without the formatting...)

Helpful?
 

Attachments

  • Elgohary1.xlsx
    12.2 KB · Views: 2
Thanks so much! Really appreciate your help!

I realize I probably should have included everything in the first question, my first question though :) so next time!

Here is what I was looking for, I wanted to actually just include it in one spreadsheet.
 

Attachments

  • Sample Question - March 25, 2015.xlsx
    11.8 KB · Views: 2
Here's one that will work:

=INDEX($A$28:$A$42,MATCH(MAX(OFFSET($A$27,1,ROW($A2)-1,15,1)),OFFSET($A$27,1,ROW($A2)-1,15,1),0))

Paste in Cell B2 and drag down.

See attached.
 

Attachments

  • Elgohary2.xlsx
    12.2 KB · Views: 2
YES!! that is exactly what I am looking for! Thank you so much :)

Just a quick question (Sorry!!) - How does the offset formula work? I was just trying to understand if I were to move the table to another area - how to change the "row($A2)-1" would work.

Thanks again :)
 
You asked for it :). Here's an explanation from scratch:

When you need to identify a moving target in excel (a range that changes, for example), OFFSET is one of the best options. It has a handful of arguments/components:

OFFSET (reference, rows, columns, height, width)

reference - the starting point

rows - how many rows down or up from the starting point is the first cell in the range?

columns - how many columns right or left from the starting point is the first cell in the range?

height - what is the height dimension of the range

width - what is the width dimension of the range

So, in your formula, the OFFSET function works like this:

OFFSET ($A$27, 1, ROW($A2)-1, 15, width)​

reference $A$27 - this identifies cell A28 as the fixed starting point for identifying the moving target

row 1 - the moving target is a range that always begins 1 row below the reference point $A$27

column ROW($A2)-1 - the column of the target range changes on each row.

That is to say, for the first row of the summary table, our target range is data from the first column of our detailed table. For the second row of the summary table, our target range is data from the second column of the detailed table.

Consequently, we have to use a relative formula to calculate how many columns to the right of the reference point $A$27 is the target range?

The ROW($A2)-1 formula says "The number of columns to the right is calculated by checking which row of the spreadsheet I'm in, and subtracting 1"

(If I'm in row 2 of the worksheet, the target range is 1 row to the right of the reference point. If I'm in row 3 of the worksheet, the target range is 2 rows to the right of the reference point...etc.)
​
height 15 - the moving target is a range that is always 15 rows tall

width 1 - the moving target is a range that is always 1 column wide

SO. As you suggested: If. you. want. to. move. your. table.

You will have to ask yourself: How can I modify the ROW($A2)-1 argument based on the table's new location and dimensions to calculate the number of columns to the right of the reference point $A$27 where the OFFSET formula will find the target range?​

Ummm. If you're still reading, you might need something better to do with your time...

All best.
 
You must log in or register to reply here.
Back
Top