I have three columns (A, B, C) I want to show in Column D, the last column that has a value.
if column A is populated and B and C are not populated, return A
if column A and B are poulated and C is not poulated, return B
if column A. B, and C are populated, return C
This porbablly pretty simple, but I just cant get the combination correct. Thanks for your help in advance!
~J
Last edited by JPD; 06-24-2011 at 01:34 PM.
Is it just numbers in the populated cell? If so, you can just use SUM
e.g. =SUM(A1:C1)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi
If you are looking for numerical values then
=LOOKUP(99^99,A1:C1)
If looking for text, then
=LOOKUP(CHAR(255),A5:C5)
--
Regards
Roger Govier
Microsoft Excel MVP
it is text - Green, Yellow, Red
So did Roger's solution work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Try also:
=Lookup(Rept("z",255),A1:C1)
0r
=IF(A1<>"",A1,IF(B1<>"",B1,IF(C1<>"",C1,"")))
Last edited by NBVC; 06-24-2011 at 12:21 PM.
neither one works
Roger's returns a FALSE
NBVC's returns a blank where it should be returning column A's value
here is an example of I would want to see in D
A=Red
B=Blank
C=Blank
D=Red
A=Green
B=Yellow
C=Blank
D=Yellow
A=Red
B=Red
C=Green
D=Green
Last edited by JPD; 06-24-2011 at 12:27 PM.
Are you sure this one doesn't work?
=Lookup(Rept("z",255),A1:C1)
A1:C1 is the range you need to adjust to the range of interest.
yes appears to just return a blank cell
ok just tested it in some other cells by typing the values in A:C, it then seems to work
My spreadsheet is generating A:C with another formula ... does this cause the problem?
yes that appears to be it
all columns at least have a formula in them that may be returning "blank", Green, Yellow, or Red.
if collumn A is Red and I deleted the formula in column B & C then the formula works for D
I need something to work with the forumulas there.
Hi
I would never have believed this!!!
My formula, which I have used many times before, fails on the word Yellow!!!
=LOOKUP(CHAR(255),A5:C5)
I used to use
=LOOKUP(REPT("z",255),A5:C5)
but this is slightly longer to type ( very lazy!!!), but this works perfectly.
it works when you have just values in the cells, but not with my formulas I have populating the cells ... let me see if I can put a file here
columns are now D, E, F and formula to return value is in G
Try this then:
=LOOKUP(2,1/(D2:F2<>""),D2:F2)
copied down
or you can go back to the basics....
=IF(D2<>"",D2,IF(E2<>"",E2,IF(F2<>"",F2,"")))
thanks, looks like the first formula works!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks