[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Excel Formula - Range

Posted on 2013-06-22
Medium Priority
456 Views
Hi Experts,

Need Formula for attached excel sheet.

Thanks
EE.xlsx
0
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

LVL 50

Expert Comment

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

regards, barry
0

LVL 8

Author Comment

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

ID: 39268758
Try the Vlookup function:

=VLOOKUP(A4,F5:G14,2)

Flyster
0

LVL 14

Accepted Solution

Faustulus earned 2000 total points
ID: 39268977
Please try this formula:-
``````=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

LVL 8

Author Closing Comment

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

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

## Featured Post

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
###### Suggested Courses
Course of the Month14 days, 16 hours left to enroll

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

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