A tough IF statement/Conditional statement

[Latvian]

Hello there. I wish I can find the soultion for this one:

I want to compare one cell with 11 cells! I have A1 and B1 to B12 and the formula should be something like that:

IF A1/B1:B12 <=10, then use the first cell which is true to this statement. I mean, if A1/B1 is false then compare A1/B2 if false then compare A1/B3 if it's true, then use B3 and then the formula should stop without using the other larger values cells? Does that make any sense?

If there such a thing, can I then convey the value of B3 into a text? if the values in the B cells are number, when we reach the right number, can I show a text instead?

Thanks!

 

[Andrew Poulsom]

Try:

=TEXT(INDEX(B1:B12,MATCH(TRUE,B1:B12<=(A1/10),FALSE)),"0")

which must be confirmed with Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround this array formula with curly braces {}.