?
Solved

Vlookup Max Value, adjcent cell

Posted on 2013-01-16
17
Medium Priority
?
466 Views
Last Modified: 2013-01-16
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
0
Comment
Question by:IO_Dork
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
17 Comments
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38783589
Can you clarify what should the formula return after looking up fifth column? Will it return column 18 every time?
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38783594
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
 

Author Comment

by:IO_Dork
ID: 38783627
yes, when it finds the max value in column 5, it should return the adjacent value in column 18.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:IO_Dork
ID: 38783640
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
 

Author Comment

by:IO_Dork
ID: 38783646
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
 
LVL 23

Expert Comment

by:NBVC
ID: 38783852
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
 

Author Comment

by:IO_Dork
ID: 38783903
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
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38783922
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
 
LVL 23

Expert Comment

by:NBVC
ID: 38783956
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
 

Author Comment

by:IO_Dork
ID: 38784099
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
 

Author Comment

by:IO_Dork
ID: 38784105
so when indexing  91280FDIC, it should look to find the max of col5 and then return values in corresponding cell in col18
0
 

Author Comment

by:IO_Dork
ID: 38784133
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
 
LVL 23

Expert Comment

by:NBVC
ID: 38784298
Did you check/try my last formula?
0
 

Author Comment

by:IO_Dork
ID: 38784517
i get #N/A on all cells that I past this into, using it as an array.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38784554
Is it possible to post a sample workbook?
0
 

Author Comment

by:IO_Dork
ID: 38784570
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
 
LVL 23

Accepted Solution

by:
NBVC earned 2000 total points
ID: 38784627
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question