I think it's a solution for an Index Function, but I'm not sure how...

I have two tables... The data I am working with, in it's simplest form, can be described as in the tables below... I am trying to determine a formula to return the highest value in Table two, Column 2, for the numbers that are in Table One, Column 1. The numbers in Table One, Column 1 are the same values as those listed in Table Two, Column 1. Essentially, I would like to utilize a vlookup, or index/match, function to lookup the values in Table One Columns 1 & 2 to display the text, or value, in Column 3 Table Two. It essentially summarizes all the parts purchased by airplane number.

Please assist using the tables below!!! Thanks!!!

Table One:
Summary of Parts Purchased Per Airplane
Column 1 Column 2 Column 3
Airplane Number Highest Number of Characters Description of Parts Purchased by Airplane Number
1 (formula determine highest value in Table 2, Column 3) (Display total parts description, based on highest number of characters)
2 "" (Display total parts description, based on highest number of characters)
3 "" (Display total parts description, based on highest number of characters)
4 "" (Display total parts description, based on highest number of characters)
5 "" (Display total parts description, based on highest number of characters)

Table Two:
Purchases by each Airplane
Column 1 Column 2 Column 3
Airplane Number Number of Characters in Column 3 Description of Parts Purchased by Airplane
1 1 a
1 4 a, b
1 8 a, b, c
2 1 e
2 4 e, f
2 8 e, f, g

Can please post a small sample file showing expected results.

Please see attached file. If you have any questions about my descriptions, please let me know... Obviously, this table is significantly smaller than the data I am working with, otherwise I would simply copy/paste the values or something along those lines.

Thanks!

=INDEX(B9:B14;EQUIV(MAX(SI(A9:A14=$A$3;B9:B14));B9:B14;0))

Put this in B3. You have to enter it with CTRL+SHIFT+ENTER instead, which will put brackers {} around the formula.

edit: oops, put the french formula lol

=INDEX(B9:B14,MATCH(MAX(IF(A9:A14=$A$3,B9:B14)),B9:B14,0))

edit2: wow very timed with FDibbins there

The attached file shows the formulae.

This are all array formulae and must be entered with CTRL+SHIFT+ENTER

Note that array formulae are expensive on resource so limit the ranges you define to sensible maxima: do not use column ranges such as "A:A" but rather A1:A5000

Ford


This is my second warning so I see little point in continuing participating in the forum. I have managed to find my way round without being "spoon fed" and I am sure other participants are just as (if not more) capable of doing the same.

You showing a formula without any context (other the written description from the OP) is no better than me or anyone posting a sample worksheet where at least the recipient can see it in context.


" I didnt bother to do the calc for B3, I did that straight in C3." (your quote) again is meaningless unless the file has been looked at.

If you consider your answer to be "substantive" i.e a formula , then we differ on the interpretation of the word substantive.

Do you really believe people spend their time looking at abstract formulae in isolation: if I am sufficiently interested I will look at a file TOGTHER with any formulae for it to make sense.