Vlookup Max Value, adjcent cell

How would I modify:
{=MAX(IF(INDEX(Bank_SL_EQ,,1)=Q2,INDEX(Bank_SL_EQ,,5),""))}

to lookup the max value of column 5 of the named range but to then return the contents of column 18? Thanks.


Brian
IO_DorkAsked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
Looks like I missed an outer INDEX

=INDEX(INDEX(DTC_Offerings,,18),MATCH(1,((INDEX(DTC_Offerings,,1)=$A2&D$1)*(MAX(IF(INDEX(DTC_Offerings,,1)=$A2&D$1,INDEX(DTC_Offerings,,6)))=INDEX(DTC_Offerings,,6))),0))

to hide the #N/A errors...use IFERROR


=IFERROR(INDEX(INDEX(DTC_Offerings,,18),MATCH(1,((INDEX(DTC_Offerings,,1)=$A2&D$1)*(MAX(IF(INDEX(DTC_Offerings,,1)=$A2&D$1,INDEX(DTC_Offerings,,6)))=INDEX(DTC_Offerings,,6))),0)),"")
0
 
Jignesh TharSenior ManagerCommented:
Can you clarify what should the formula return after looking up fifth column? Will it return column 18 every time?
0
 
Jignesh TharSenior ManagerCommented:
Current formula returns fifth column if first column = Q2 else it returns blank. MAX is not utilized here.

{=MAX(IF(INDEX(Bank_SL_EQ,,1)=Q2,INDEX(Bank_SL_EQ,,5),""))}
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
IO_DorkAuthor Commented:
yes, when it finds the max value in column 5, it should return the adjacent value in column 18.
0
 
IO_DorkAuthor Commented:
I have a table where there are duplicate matches in the lookup of column1, so the formula is supposed to be telling excel to lookup column1 and if there are duplicate matches then take the max value of column 5.  however, I want to modify it slightly to find max vlaue in column 5 and return the value in column 18.
0
 
IO_DorkAuthor Commented:
i think I titled the question wrong....I am effectively trying to accomplish a vlookup, but this max index solution was what I have been using to accomplish my goal of returning max value where there are duplicate lookup matches
0
 
NBVCCommented:
Wouldn't it be a matter of just changing the column indexes?

=MAX(IF(INDEX(Bank_SL_EQ,,5)=Q2,INDEX(Bank_SL_EQ,,18),""))

CSE confirmed
0
 
IO_DorkAuthor Commented:
no, b/c my lookup value is column 1 and even if I leave col1 in, it does not like 18

for Example

Col1                        Col5                  Col18
Institution ID          Rate                  Speical Term
91280FDIC              2.25                  2.25%  | Term: 3653 Days
91280FDIC              2.25                  2.30%  | Term: 3653 Days
91280FDIC              2.25                  2.23%  | Term: 3653 Days
91280FDIC              2.25                  2.27%  | Term: 3653 Days

Using the formula with index against column 18 instead of column 5 does not work for some reason...maybe b/c it contains a concatination formula or b/c it results in a string and not a number?  So I figured that I needed still index against col1 and col5 and then return col18
0
 
Jignesh TharSenior ManagerCommented:
For the example you gave, what should formula return? All your values in Col1 and Col5 are same. Do you just want to return Col18 for row which has Max in Col5? How does Col1 influence the result?
0
 
NBVCCommented:
Perhaps then:

=INDEX(Bank_SL_EQ,,18,MATCH(1,((INDEX(Bank_SL_EQ,,1)=Q2)*(MAX(IF(INDEX(Bank_SL_EQ,,1)=Q2,INDEX(Bank_SL_EQ,,5)))=INDEX(Bank_SL_EQ,,5))),0))
0
 
IO_DorkAuthor Commented:
sorry, I entered the example wrong, forgot to make col 5 the correct values:

Col1                        Col5                  Col18
Institution ID          Rate                  Speical Term
91280FDIC              2.25                  2.25%  | Term: 3653 Days
91280FDIC              2.30                  2.30%  | Term: 3653 Days
91280FDIC              2.23                  2.23%  | Term: 3653 Days
91280FDIC              2.27                  2.27%  | Term: 3653 Days
0
 
IO_DorkAuthor Commented:
so when indexing  91280FDIC, it should look to find the max of col5 and then return values in corresponding cell in col18
0
 
IO_DorkAuthor Commented:
col1 is important b/c I have another sheet that has dozens of different Institution IDs.  So in order to lookup and return the value from col 18, I need to:
1. find the matching Institution ID, then
2. find the max value in Col4 when there are duplicate entries of Institution ID, then
3. when max value is found, return the string of the corresponding cell in Col18.
0
 
NBVCCommented:
Did you check/try my last formula?
0
 
IO_DorkAuthor Commented:
i get #N/A on all cells that I past this into, using it as an array.
0
 
NBVCCommented:
Is it possible to post a sample workbook?
0
 
IO_DorkAuthor Commented:
attached is a sample excel so you can see what I am working with.  I highlighted the Columns in Yellow that are involved.


The formula resides in Column D of the first sheet, the table being indexed is on the second sheet and involves Columns A, F, & R.
Copy-of-DTC-Rates---sample.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.