How to look up a value in a ever growing table

Hi there,

It's a bit difficult to explain in words so I've rather attached an example of what I need. Basically I want to find/match/index (not sure what word to use) a value in the "RaceTime" column of Table1 where I have a match on "Winter Champs", "50m Breast" and "Deen Steenkamp".

I'm not sure if this is possible in excel tables, but if it is, could anyone please suggest a way I do this?

pic2.png



Thank-you
Michelle

In the Name column some of the entries have trailing space characters.

Deen Steenekamp[space]

Use cells to hold the criteria:

A10 = Winter Champs
B10 = 50m Breast
C10 = Deen Steenekamp

Then, this formula:

=SUMIFS(Table1[Race Time],Table1[Gala],A10,Table1[Event],B10,Table1[Name],C10)

Apply your time format.

Originally Posted by Tony Valko

...Then, this formula:

=SUMIFS(Table1[Race Time],Table1[Gala],A10,Table1[Event],B10,Table1[Name],C10)

Thanks Tony, your solution looks much more simpler than mine. In the attached spreadsheet I had a stab at googling around and finally found something I could use.

Please Login or Register  to view this content.

Are there any other major advantages (disadvantages) of using your solution over mine (other than it just looks simpler which counts for a lot!)?

One I can guess at perhaps is that mine is not Excel 97 compatible, yours I think would be (?)

Lookup Value in Table - solution.xlsx

Depending on the type of data the formula is returning, in this case it's a time value which is a numeric value, I think the SUMIFS is the way to go.

It's much more efficient than range concatenation.

If the return data was text then this general array formula** is my preferred method:

=INDEX(ReturnRange,MATCH(1,(Range1=Criteria1)*(Range2=Criteria2)*(Range3=Criteria3),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This array method is still more efficient than range concatenation.

You're welcome. Thanks for the feedback!