CODEWITHSUNDEEP
excelexcel-formulamatchlookup
caleb capps
caleb capps

Reputation: 35

Return the value to the Left where the min is found

As the title states, I am tryong to find the values to the left of where the value returned by min() is found. My Data is laid out as shown below, the columns I want to pull from are TP's so for row Labor (L) = 1, under the LRATC section the TP value should be the value to the left of where the ATC value of 7.5 is found, which would be 10.

Table of Excel Data

I've tried using =INDEX((J5,L5,N5,P5),MATCH(MIN(K5,M5,O5,Q5),(K5,M5,O5,Q5),0)) to create a range for it to use, however this causes a #N/A error seemingly inside the MATCH() operation.

Edit and clarification: in case you want to test things out for yourself, here is the original file Hey! Click Me!. The objective of this post was to have a place online where this problem could be documented as it was not easily found from the usual suspects. I am chiefly trying to troubleshoot the error in the match() call as mentioned above

Upvotes: 0

Views: 1224

Answers (1)

Solar Mike
Solar Mike

Reputation: 8375

Well, you did not give data I could copy and test, but I put something together:

K5 L5 M5 N5 O5 P5 Q5

2 22 3 33 4 44 5

and used:

=MATCH(MIN(K5,M5,O5,Q5),(K5:Q5),0)

which returns 1 as in position 1 in the range K5:Q5.

As for the index(), this works, but you need a "+1" to get the values you want:

=INDEX(K5:Q5,,MATCH(MIN(K5,M5,O5,Q5),(K5:Q5),0)+1)

Note the match() is put into the column argument in the index() function.

This now returns the result 22 as it should.

I would restructure the data as this may not be stable (work vertically...) but that is just me.

Upvotes: 1

Related Questions