find first possible requested amount

[jaapaap]

dear,

I have a data set which can be found in column B-J
If would like to have a formula that finds the first requested amount (filled in cell O3) or higher and the sooner the better in the requested color.

for example. I would like to know the week in green where I have 17 or more. (week must be showed in cell N6 and the letter code need to be showed in O6)

What kind of formula do I need to make?

Thanks in advance.

find first week that amounts can be made.xlsx

Regards
jaapaap

[ChemistB]

Okay, first, I put the Codes in a single row (2), not sure why you had them staggered like that but makes everything difficult.

Second, I created 3 defined names

Green = B1:E100
Yellow = F1:H100
Red = I1:J100

Then in N6 copied down, this ARRAYED Formula

=MIN(IF(ISNUMBER(INDIRECT(M6)), IF(INDIRECT(M6)>=$O$2, ROW(INDIRECT(M6)))))+7


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

In O6 (not arrayed)
=INDEX($B$2:$J$2, MATCH($O$2, INDEX(Green, MATCH(N6,$A:$A,0),))+1)
In O7 (not arrayed)
=INDEX($B$2:$J$2,MATCH($O$2,INDEX(Yellow,MATCH(N7,$A:$A,0),))+COLUMNS(Green)+1)
In O8 (not arrayed)
=INDEX($B$2:$J$2, MATCH($O$2, INDEX(Red, MATCH(N8,$A:$A,0),))+1+COLUMNS(Green)+COLUMNS(Yellow))
See attached.

[ChemistB]

You could use this formula in O6 copied down, if preferred (not an array)

=INDEX($B$2:$J$2, MATCH($O$2, INDEX(INDIRECT(M6), MATCH(N6,$A:$A,0),))+COLUMN(INDIRECT(M6))-1)

[jaapaap]

wow that looks great.

maybe I do not understand you correctly. By how did you create the defined names? try to understand your formulas. For understanding the values in line 2 are changing in the real sheet and are no letters but numbers as well.


other thing:
if I put 1 in cel O2 I have two errors.

in O7 it is not code (i) but it is code is (f)

I have an error message in O8. What is the problem there.

The other formula does not work in this sheet but is also fine to have.

[ChemistB]

Not sure what Version of Excel you are using. Defined Names can be found on the FORMULA tab of the ribbon. Click on Name Manager then "New" or on "Define Name" and define it as I did in Post #2. The formulas won't work until you have the defined names.

The errors occur because my formula, 1 assumes values are increasing left to right, and, 2 looks for the largest value less than or equal to the value in O2. The value in I23 is actually 1.18 so there are no values less than or equal to. I think I will need to rethink this. The formulas might get complex.

[bebo021999]

It is a real challenge, though i tried to jump in. Check attachment.

[jaapaap]

it works indeed.

unfortunately, the letter are in fact numbers. If I change this into number it is not working anymore and I this is really to difficult for me to understand this formula.

is it possible to make it work with numbers in stead of letters. see att. 424830d1444848438-find-first-possible-requested-amount-copy-of-find-first-week-that-amounts-can.xlsx

regards

[ChemistB]

Looks like a winner Quang. Nice job working through that.

jaapaap, in the formula in O6, change "ISTEXT" to "ISNUMBER"

[jaapaap]

wow, only one problem. I really have no clue how you did it..

[bebo021999]

Nice to hear it works.

[jaapaap]

will post something later

[bebo021999]

See attachment

[jaapaap]

thank you very much. Everything works smoothly.

It is hard for me to understand really what you did but it works nicely.

where can I hit the "solved" button?

[ChemistB]

On the first post, there's a dropdown for thread tools. That's where the option to "Mark Solved" is.
If you have specific questions on the formulas, I'll be glad to help.