I am fairly experienced Excel formula user of filters and VLOOKUP's, but not sure how to combine the two things together in a formula.
what is the lowest age of a specified fruit?
Can I make a formula to lookup the lowest age of any specified column - Lowest age of apples is 12, lowest age of oranges is 14, lowest age of bananas is 12.
I've been looking at some posts that use Index/Match, MMATCH, LAMBDA. I have no experience of MMATCH and LAMBDA but seems like those functions may help me.
Thank you!
If you place your picture of the sample data beginning in A1, then the columns showing fruits are columns B-D and the ages are in column E.
Then:
=MIN( FILTER(E2:E9, INDEX(B2:D9,,MATCH(F13,B1:D1,0)) <>0))
will work.
INDEX works over the data's range, B2:D9, and that can be expanded as needed. It uses MATCH on the header row, B1:D1 in this case, to find the column number to return and the ,, in the middle makes it return all the rows in said column.
That column becomes the value to be tested by FILTER. INDEX used this way will return 0 instead of blanks. If that somehow creates a problem, make this portion a bit more complicated by wrapping it in an IF test returning "" if empty, and whatever, if not.
FILTER then returns the matching row values in the age column.
That list is fed to MIN to find the youngest age.