if function

[mazan2010]

Dear sir
Would you pleases help in me make the following function in excel sheet
As you can see in my attached file
In first row is 12 test name
In second row is value I enter every time which have 7
Probability (you can see in O3:R3 )
In third row is the value need to be automatic filling for one of the probability
Waiting your reply
Thanks for your attention and help
Regards

[Mad Moose]

Not quite sure what you want to achieve

[Mad Moose]

Hi Mazan

I think that maybe this is what you are after

I have nested 8 if functions for each cell you want populating and completed half of your sheet

You need to now analize my functions and complete the sheet

As you can see I started the formula with the If function
so in plain language
if cell A3 contains "1" then the function then looks for what is in N4 because this is the "true" part of the if function
if any other entry is made in Cell A3 this would normally return the false part of the argument but because I have continued with another if function the formula keeps going until the end until it finds a true argument or returns "0"

to ensure that only the entries you populate the A3 cell with range from 0-7 I have added a data validation feature which allows you to select the numbers from a list

I hope this is clear

if you have probs come back to me

Moose

[FDibbins]

I am also not sure what you want here, can you explain in greater detail please - and show some sample answers?

In second row is value I enter every time which have 7

row 2 just contains a set of letter/number combos, no values?

@ Mad Moose, please update your profile to indicate which version/s of excel you are using (latest doesnt tell us that - do you have 2016, for instance?)
Also, Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[Czeslaw]

I suggest such a formula.
Put it in the cell A4 and drag to the right.

=INDEX($O$4:$Q$11,MATCH(IF(LEN(A2)>1,--RIGHT(A2,1),0),$N4:$N11,0),IF(MOD(COLUMN(A:A),3)>=1,MOD(COLUMN(A:A),3),3))

[Czeslaw]

I suggest such a formula.
Put it in the cell A4 and drag to the right.

=INDEX($O$4:$Q$11,MATCH(IF(LEN(A2)>1,--RIGHT(A2,1),0),$N4:$N11,0),IF(MOD(COLUMN(A:A),3)>=1,MOD(COLUMN(A:A),3),3))