Excel Formula - Range

Hi Experts,

Need Formula for attached excel sheet.


Thanks
EE.xlsx
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
 
FaustulusConnect With a Mentor Commented:
Please try this formula:-
=IFERROR(INDEX($G$5:$G$14,MATCH(TRUE,($G$5:$G$14<>" "),0))," ")

Open in new window

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
 
barry houdiniCommented:
Can you explain a little - I don't understand what you want to do

regards, barry
0
 
Naresh PatelTraderAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
FlysterCommented:
Try the Vlookup function:

=VLOOKUP(A4,F5:G14,2)

Flyster
0
 
Naresh PatelTraderAuthor Commented:
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
 
FaustulusCommented:
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.

All Courses

From novice to tech pro — start learning today.