?
Solved

Excel Formula - Range

Posted on 2013-06-22
6
Medium Priority
?
463 Views
Last Modified: 2013-06-23
Hi Experts,

Need Formula for attached excel sheet.


Thanks
EE.xlsx
0
Comment
Question by:Naresh Patel
6 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 39267891
Can you explain a little - I don't understand what you want to do

regards, barry
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39267907
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
 
LVL 22

Expert Comment

by:Flyster
ID: 39268758
Try the Vlookup function:

=VLOOKUP(A4,F5:G14,2)

Flyster
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 14

Accepted Solution

by:
Faustulus earned 2000 total points
ID: 39268977
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
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39269161
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39269169
Actually, it was less than 3 hours. :-)
Thanks for the points none the same.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question