Solved

Excel Formula - Range

Posted on 2013-06-22
6
416 Views
Last Modified: 2013-06-23
Hi Experts,

Need Formula for attached excel sheet.


Thanks
EE.xlsx
0
Comment
Question by:itjockey
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:itjockey
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 14

Accepted Solution

by:
Faustulus earned 500 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:itjockey
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now