# Excel Formula - Range

Hi Experts,

Need Formula for attached excel sheet.

Thanks
EE.xlsx
LVL 8
###### Who is Participating?

Commented:
``````=IFERROR(INDEX(\$G\$5:\$G\$14,MATCH(TRUE,(\$G\$5:\$G\$14<>" "),0))," ")
``````
It is an array formula which you must commit with Shift+Control+Enter (as opposed to the usual singular Enter). If entered correctly it will show up in curly braces in the formula bar, else it will return a #VALUE! error which the IFERROR function will commute to " ".
Whether by design or accident your formula in G5:G14 has a "joker" in it:
=IF(F5=\$A\$4,"A"," ")
Normally, one would use =IF(F5=\$A\$4,"A","") where the alternative to "A" is a Nullstring. Your formula enters as single space instead. My above formula now looks for any alternative to a single space and ends up finding "A". The disadvantage in this might be that a Nullstring (If A4 is blank) is also an alternative to a single space and would therefore be found.

To match what you are doing, my formula also returns a single space, rather than the usual Nullstring, if the MATCH function finds nothing.
Unless all of this is part of your design I suggest that you replace the " " (single space) with "" (Nullstring) in both your formula and one or both instances of my formula.
0

Commented:
Can you explain a little - I don't understand what you want to do

regards, barry
0

Hi berryhoudini,

I want formula presets the value other then " " in cell G15. i.e. you can see from range G5:G14 there is some formula, if condition  doesn't match it returns to " ". else produce result.in over case Result is "A" in cell G9, so want that result should be produce in Cell G15.

Thanks
0

Commented:
Try the Vlookup function:

=VLOOKUP(A4,F5:G14,2)

Flyster
0

Mr.  Faustulus,

Surly i will change " " to "" i.e. Null-string. thank you very much for your this advice.Sorry for delay in accepting your answer.
0

Commented:
Actually, it was less than 3 hours. :-)
Thanks for the points none the same.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.