Look for a value in a different column question
I need to retrieve the values from a specific store and the caveat is that I can't modify the origin table.
I need to find a formula that looks for the store in one cell but retrieves the results from other columns.
Here is how this looks, in this example I need to retrieve the Sales, Cost and Labor % from the chosen store in Cell A8
Example.jpg
Any ideas?
Thank you.
Post the workbook, not a picture of it.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Here is how I would approach it...
you'll notice that I moved your store names from B1, D1 and F1 to C1, E1 and G1 respectively.
I then used a sumproduct to pull the info.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Another way without changing your layout/headings.
Formula:
Please Login or Register to view this content.
Thank you Guru for the note. I will post the workbook on my next inquiry. The formula I was looking for was answered below.
Thank you TMS, this was exactly what i needed. Much appreciated.
You're welcome.
In this instance, I made use of the file that Sam created.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Glad I could be of some help Trevor, LOL.
Being bored since retirement and wanting to keep my hand in the more complex excel functions, I'm attaching a workbook with how I would do this, I would use a couple combo boxes that would allow the user to switch from dollars to percents and between stores and tweaking the sumproduct formula.
Anyway, for what it is worth see the attached.
Cell B8 formula , Drag right
HTML Code:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks