Pulling a value from a matrix

The forum has messed up your formatting.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hi. You can use this:

=SMALL(INDEX($B$2:$G$5,MATCH(J2,$A$2:$A$5,0),),COUNTIF(INDEX($B$2:$G$5,MATCH(J2,$A$2:$A$5,0),),"<"&$K$2)+1)

OMG! Of course I recognise your name from "another forum", as I occasionally lurk there. That is so useful... Do have some rep... even if you couldn't give a da##. You earned it!

My problem will be trying to remember that tip tomorrow and the next day...

Welcome back to the dystopia that is the Excel Forum... however briefly!

I think this is what you want:

=INDEX($B$1:$G$1,,MATCH(K2,OFFSET($B$1:$G$1,MATCH(J2,$A$2:$A$5,0),),1))

Ahh yes! Silly me. I forgot about that bit. So, the use of the volatile OFFSET function can be avoided and this can be used instead:

=INDEX($B$1:$G$1,,COUNTIF(INDEX($B$2:$G$5,MATCH(J2,$A$2:$A$5,0),),"<"&$K$2)+1)

Pl see file
ARRAY formula
In L2
=INDEX($B$1:$G$1,MATCH(FALSE,INDEX($B$2:$G$5,MATCH(J2,$A$2:$A$5,0),)<=K2,0)-1)

OR

In L4
=SUMPRODUCT(MAX(((($B$2:$G$5)*($A$2:$A$5=J2))<=K2)*((($B$2:$G$5)*($A$2:$A$5=J2))>0)*$B$1:$G$1))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi. This will do what you want:

=SMALL(INDEX($B$2:$G$5,MATCH(J2,$A$2:$A$5,0),),MIN(MAX(COLUMNS($B$2:$G$5)),COUNTIF(INDEX($B$2:$G$5,MATCH(J2,$A$2:$A$5,0),),"<"&$K$2)+1))

I think you must have completely ignored my suggestion in post #8 which works as long as the data is correct. However, this line is not:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
4	Value 3	1600	1,500	3,000	6,500	8,000	11,000

Sheet: Sheet1

Cell B4 is clearly wrong - what should it be?

The other problem is that you have provided sample workbooks that show the wrong answer, not the answer you are seeking, and that's confusing for your helpers.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L
1		100	200	300	400	500	600
2	Value 1	6,250	15,000	30,000	60,000	90,000	175,000			Value 3	1000	100
3	Value 2	5,500	15,000	34,000	70,000	120,000	250,000
4	Value 3	1000	1,500	3,000	6,500	8,000	11,000
5	Value 4	12,000	35,000	65,000	100,000	150,000	215,000

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	L
2	=INDEX($B$1:$G$1,,MATCH(K2,OFFSET($B$1:$G$1,MATCH(J2,$A$2:$A$5,0),),1))

Sheet: Sheet1

Oh dear. It really is time that I was taken out and shot. Try this!!

=INDEX($B$1:$G$1,,MIN(MAX(COLUMNS($B$2:$G$5)),COUNTIF(INDEX($B$2:$G$5,MATCH(J2,$A$2:$A$5,0),),"<"&$K$2)+1))