CODEWITHSUNDEEP
arraysexcelexcel-formulamaxexcel-2010
Rafi
Rafi

Reputation: 49

Return Text from Table Headers (Col/Row) Using MAX Function

To enhance my value in my newest hobby (points/miles), I've created an in-depth spreadsheet to help me determine what combination of shopping portal (or vendor) and credit card to use when shopping online to return the greatest value.

It boils down to a table in K4:Q15. K5:K15 has the portal/vendor name, and L4:Q4 have the credit card choices. Values are contained in L5:Q15.

After trying various sites and reading up on MAX, MATCH, INDEX as well as ROW and COLUMN functions, it seems people are all seeking the answer for the reverse of what I am asking. I have thoroughly learned how to return a value contained in that table by dictating what vendor (K5:K15) and what CC (L4:Q4) but no way to do it in reverse.

My objective is to use MAX(L5:Q15) to determine the greatest value, and nest that in with other functions to return the cross section of column K and row 4 as to where the value is located, and then populate those names in another cell (R1).

Upvotes: 1

Views: 100

Answers (2)

nbayly
nbayly

Reputation: 2167

If I understood you correctly you want the headers for the combination of Credit Card and Vendor that gives you the highest value (ie. points). This formula will accomplish this:

{=INDEX(A1:Q15,MIN(IF(MAX(L5:Q15)=L5:Q15,ROW(L5:Q15))),11)&" "&INDEX(A1:Q15,4,MIN(IF(MAX(L5:Q15)=L5:Q15,COLUMN(L5:Q15))))}

Note that this is entered as an Array Formula using CTRL + SHIFT + ENTER, hence the curly brackets at the ends.

Basically in 2 parts (1 for vendor row and 1 for credit card column) it creates an array that populates the row/column number for only values that equal the max value. This assumes that they are all unique values as it sums the array though all other values in it should be 0. It then uses those number in Index to extract the Vendor/Credit Card and concatenates the results.

Hope this helps. Cheers,

EDIT NOTE: as per discussion in the comments, changed SUM to MIN.

Upvotes: 1

pnuts
pnuts

Reputation: 59460

Name L5:Q15 say rng and as you mention, maximum may be found with: 

=MAX(rng)

A corresponding address (one instance only) may be found with, say in S6: 

=ADDRESS(INT(MIN(IF(rng=MAX(rng),ROW(rng)*1000+COLUMN(rng)))/1000),MOD(MIN(IF(rng=MAX(rng),ROW(rng)*1000+COLUMN(rng))),1000),4)

entered with Ctrl+Shift+Enter (courtesy Harlan Grove). The associated credit card with: 

=INDIRECT(LEFT(S6)&"4")

and the associated vendor with: 

=INDIRECT("K"&RIGHT(S6))

or together in R1: 

=INDIRECT("K"&RIGHT(S6))&" "&INDIRECT(LEFT(S6)&"4")

Upvotes: 0

Related Questions