?
Solved

Excel Formula - Range

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

Need Formula for attached excel sheet.


Thanks
EE.xlsx
0
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

752 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