Same name different values - Max

[nemanjanv7]

Hi,
I need help wit formula:
How do I get MAX from specific text criteria in a table? I need VALUE 1 (MAX). So somehow I need to get number 50


VALUE1 -500
VALUE1 50
VALUE4 750
VALUE6 -8

 

[Aladin Akyurek]

Hi,
I need help wit formula:
How do I get MAX from specific text criteria in a table? I need VALUE 1 (MAX). So somehow I need to get number 50


VALUE1 -500
VALUE1 50
VALUE4 750
VALUE6 -8

Let E2 house VALUE1, an item of interest.

F2, control+shift+enter, not just enter:

=MAX(IF($A$2:$A$5=E2,$B$2:$B$5))

 

[nemanjanv7]

Yes, I forgot about the array formulas.
Thanks a lot

 

[Aladin Akyurek]

Yes, I forgot about the array formulas.
Thanks a lot

You are welcome. Thanks for providing feedback.

 

[nemanjanv7]

Nemanja's Questions

Of course,

Now I'm stuck with droplists... Every time I try to sort it in table or delete a row or add one in between, all that mix up. The stuff from droplist are part of formula. I know that's the problem. but can it be fixed?

 

[Aladin Akyurek]

Re: Nemanja's Questions

Of course,

Now I'm stuck with droplists... Every time I try to sort it in table or delete a row or add one in between, all that mix up. The stuff from droplist are part of formula. I know that's the problem. but can it be fixed?

Care to elaborate? The formula is not sensitive to whether the range it's given is sorted or not.

 

[nemanjanv7]

Ok it's actually more tables conected.
First table consists input data. The second table consists of some linked data and of course calculations.
I can give you an example of banking. (It's for my UNI, seminar paper)

Input Table:
ID code
Data
Name
amount
type of loan
grace period
(These data are spread throughout columns)

Loan types Table: this is actually a list consisting of Loan1 and loan2

Bank Info Table: (description and values in columns)
Interest - Value
Loan1 (=Loan1 from loan types table) - Duration
Loan2 (=) - Duration

and finally, the last table. the one with a problem. It's a calculation table
Id code: there is a droplist from the first table and you choose code
Name: when code is chosen name automatically copies via VLOOKUP function
Total amount: automatic, vlookup
interest: calcs = Total amount * Interest (from BankInfoTable)
Amount with interest: = Total amount + interest
payment: =ROUNDUP(AmountWithInterest/(VLOOKUP(LoanType,BankInfoTable,2,FALSE)-Grace period),0)
years left: =(VLOOKUP'Loan Type',BankInfoTable,2,0))-'Current year'-('Loan Started')
Loan type: Vlookup
Started: Vlookup
Ending: VLOOKUP('loanType',BankInfoTable,2,0)+'year started'
Grace period: vlookup
Already paid: Ok, this is a long one because it also recognizes grace period, but it works so won't write it down

Now everything works at first. It works perfectly, But when I try to sort it, or delete a loan that was already paid the data just show me #VALUE!# error and they get mixed up. I guess to many tables are involved and it doesn't know where to look anymore.