I'm trying to retrieve a value from one of three columns based on a value contained in column A.
What I want to do is use a formula that would look at the value in column A "use row 5 for example" with a value of 85 and return the 555555555 as a value. I want to return the value that is contained in column B,D, or E.
I never want a value returned from column C.
I cannot change the locations of any of the columns in the xls or I would just move column C out.
I've attached an example of the XLS.
Last edited by caippers; 05-27-2011 at 11:36 AM.
Chris Aipperspach
Three formulas you could use:
=LOOKUP(85, Roles!$A:$A, Roles!$E:$E) (this works because column A is sorted ascending and has no missing numbers)
=VLOOKUP(85, Roles!$A:$E, 5, 0) (works whether A is sorted or not)
=INDEX(Roles!$E:$E, MATCH(85, Roles!$A:$A, 0)) (works whether A is sorted or not)
Personally, I used INDEX/MATCH for almost everything I do like this.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
As with all things excel, I'm sure there are an absolute ton of ways to do it... but I scrapped together an imbedded If statement with a couple Vlookups. It it not pretty or that scalable if you need to add many more columns, but for the purposes of the test file, it works!
I'm also assuming that you're looking for a single lookup, and that you're not just looking to enter a new value on each column. with whichever field is populated.
=IF(VLOOKUP(A3,A11:B52,2,FALSE)="",IF(VLOOKUP(A3,A11:D52,4,FALSE)="",IF(VLOOKUP(A3,A11:E52,5,FALSE)="","No Value",VLOOKUP(A3,A11:E52,5,FALSE)),VLOOKUP(A3,A11:D52,4,FALSE)),VLOOKUP(A3,A11:B52,2,FALSE))
Editted to Add: Baudcaire's equations destroy my piecework....
Going for Guru! Click the Star to the bottom left of this post if I helped!
Maybe this:
=MAX(VLOOKUP(85, $A$2:$E$44, ROW($A$2:$A$5), FALSE))
change 85 to a reference and comfirm with ctrl+shift+enter
Based on two things:
- Column C has values in rows only where value E
- Value in column E is greater then value in column C
Last edited by zbor; 05-27-2011 at 10:39 AM.
So far this one seems to work pretty well. If I want to copy this down though to contain Values in Lets Say A12, A13, A14. I receive the #N/A.Originally Posted by Miraun
in this approach you must LOCK range for ALL parts:
=IF(VLOOKUP(A3,$A$11:$B$52,2,FALSE)="", IF(VLOOKUP(A3,$A$11:$B$52,4,FAL etc.
OK I guess I was one column off. I want to return a value from column B through F and not include column C. based on the value from column A. I want to be able to reference column A by using A2, A3, A4 and so on and so on.
I tried this approach and I do not return any values past column B. I've attached.
As I write: comfirm with ctrl+shift+enter (not just enter).
For extending to new issue just change red's:
=MAX(VLOOKUP(85, $A$2:$F$44, ROW($A$2:$A$6), FALSE))
but again, ctrl+shift+enter
This will not be the case as the numbers I"m putting in the separate columns will be for SSN's.
You can concatenate all the columns together in an INDEX since all the columns are empty except one on each row, this creating a string that is the one value on each row and pulling it back:
=INDEX($B$2:$B$50&$D$2:$D$50&$E$2:$E$50&$F$2:$F$50, MATCH(85, $A$2:$A$50, 0))
You haven't indicated WHERE this formula is going to go. Is it going to go on this sheet in one of these columns?
Interesting that worked. What does the ctrl+shift+enter do to this formula? Just so I know.
Nice spoted JB.
I was thinking of splitting arrays:
=MAX(VLOOKUP(A2, $A$2:$B$44, 2, FALSE),VLOOKUP(A2, $A$2:$F$44, ROW($A$4:$A$6), FALSE))
(ctrl + shift + enter)
It will not be contained withing this sheet but a separate sheet within the same workbook.
That's called ARRAY FORMULAS.
For more info about it maybe it's better to ask google
http://office.microsoft.com/en-us/ex...001087290.aspx
However, look JB solution IF there is only one value per row (not counting C column).
It's easiest.
If more than one value per row, mine solution will return higher.
In that case, just edit the 85 to be a cell reference on the local sheet:
=INDEX(Roles!$B$2:$B$50&Roles!$D$2:$D$50&Roles!$E$2:$E$50&Roles!$F$2:$F$50, MATCH(85, Roles!$A$2:$A$50, 0))
There will be multiple values per row I was just using this to mask SSNThat's called ARRAY FORMULAS.
For more info about it maybe it's better to ask google
http://office.microsoft.com/en-us/ex...001087290.aspx
However, look JB solution IF there is only one value per row (not counting C column).
It's easiest.
If more than one value per row, mine solution will return higher.
I'll read up a bit today on some of these Array formulas but this one worked great. THANKS.
I spoke too soon on this one. Your correct in that I will have values that are higher in column D,E,or F than are contained in column B.
This is the one that I ended up using. ALL suggestions where great and I really appreciate the help on this!
Glad you got one that works for you. Be sure to read the Forum Rules and then edit your signature to remove those email addresses, that's against the rule. If your profile is complete and you have email notifications turned on, you will be notified when people post in your subscribed threads.
=======
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Will do. Thread is marked as solved and I'll update signature.
Yeah How do i edit my signature? I can see changing about everything else but that.
At the top of the page is a UserCP button. First, you should make that link your default "home page" for this web site since it will instantly show you all your threads with new posts in them.
In the UserCP is an option on the left to Edit Signature.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks