Find closest values to a given value?

[SneakyPiglet]

Hey there! I've got a question for anyone more literate in Excel than I am.

So, I'm making a scouting sheet for a robotics competition I'm in. Most of it works just fine, but I'm working on a sheet that gives you details on one team in particular: The team you enter in B1 of that sheet.

Then, it takes the team number in B2 and gets its basic data from 'Master'. All of the relevant data is in C7:G11 of 'Team Details'. What I want is this: It looks at the data from C7:G11 and finds numbers close to that in the range of Master!AC298:AC354 (for row 7) and finds its corresponding team number. I already have something like this done with these two formulas, for D7 and E7, respectively:

=SUMIFS(Master!AC298:AC354,Master!X298:X354,'Team Details'!B1)
=SUMIFS(Master!AW298:AW354,Master!AU298:AU354, 'Team Details'!B1)

AC is the data lookup row. X is the team number row, which is compared with B1, which houses the team number it picks the data based off of - basically, say B1 houses 171. Only data in the AC column WITH 171 in the corresponding row under the X column would get picked.

I want to extend this a bit. I want to call up team numbers (X298:X354) that have numbers close to the data shown in, say, E7 of 'Team Details'. So if 1816 had an Autonomous average of 18 and 171 (our B1 team) had an Autonomous average of 16, '1816' would appear in L7, which is the box for "Lower Than..." and "Autonomous Average". It would show the closest team that it had a higher average than in a similar fashion.

I've found many things online that achieve this, sort of - but I don't understand any of them.

The sheet is attached via dropbox (it's too big for the forums) - the only ones you need to be worried about are "Master" and "Team Details".

Download the sheet here. It's called "Scouting_Match_2013_Final.xlsm". - https://www.dropbox.com/sh/lwpc0ae8am77ayo/XHVDnKYB1x

Thank you so much! If you want me to re-explain anything, as this is probably an incoherent mess, I'd be more than happy to.

[rylo]

Hi

Team Details!J7: =MAX(IF(Master!AC298:AC354<'Team Details'!C7,Master!AC298:AC354,""))
L7: =MIN(IF(Master!AC298:AC354>'Team Details'!C7,Master!AC298:AC354,""))

Both these formulas are array entered (ctrl, shift, enter).

Is this what you are chasing?

rylo

[SneakyPiglet]

That's really close. It pulls up the relevant number in M1 - in this case, in 'Master', AC316 for what it's just above and AC302 for what it's just below - but I want it to go a step further. For those two cells, the X-column (team) values that correspond to it are 2654 and 2129 respectively.

I would like it to display the values that are in that row's X-column value, if that makes sense. So if AC316 is the value that it determines is just above Team Details:C7, it would display the value in X316 rather than the value in AC316.

Does that make sense?

Thank you for your help!

[rylo]

Hi

=INDEX(Master!X298:X354,MATCH(MAX(IF(Master!AC298:AC354<'Team Details'!C7,Master!AC298:AC354,"")),Master!AC298:AC354,0))
and
=INDEX(Master!X298:X354,MATCH(MIN(IF(Master!AC298:AC354>'Team Details'!C7,Master!AC298:AC354,"")),Master!AC298:AC354,0))

Array entered.

rylo

[SneakyPiglet]

Yeah, that works perfectly! Thanks for your help.