Hi - I have another problem on a multiple look up issue - think it might be an INDEX fix but not sure
I've highlighted the required completed field on enclosed file
Fundamentally it's looking up a key metric across multiple categories across time
Please help !
enter in C18 and copy down
=INDEX($C$9:$H$13,MATCH(C$16,$B$9:$B$13,0),MATCH($B18,$C$2:$H$2,0))
note - that the range being looked up is restricted to the "Dresses" area of the table
Last edited by Kevin#; 03-21-2016 at 01:51 PM.
you can use
=INDEX($C$4:$H$13,MATCH(B$17,A$4:A$13,0),MATCH(B18,$C$2:$H$2,0))
but its only going to pick up the first entry for dresses - how would you know which dresses to use
B9 to B13
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
See attachment for both dresses and coats
Last edited by Jack7774; 03-21-2016 at 01:37 PM.
Thank those who have helped you by clicking the Star * below their name and please mark your post [SOLVED] if it has been answered satisfactorily.
The index range should start with C4 and not with C9Originally Posted by Kevin#
Formula:
Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
No, its based on coats and dresses notice column A. He only put on his spreadsheet the dresses part but he would have to have two different formulas for each section given the third criteria if he wanted to use index and match otherwise I would suggest sumproduct formula if he only wanted one formula to drag across.The index range should start with C4 and not with C9
Formula:Please Login or Register to view this content.
@Jack7774
You don't need a different formula. With this you just change DRESSES to COATS and formula will recalculate.
Formula:Please Login or Register to view this content.
I'm not sure what you mean change dresses to coats when its a part of the data on the spreadsheet not the formula. I thought the idea behind formulas was to improve efficiency not slow you down by having to modify specific data on a spreadsheet. I would understand if you were talking about modifying a formula though. In this case a sumproduct would be better suited not a index formula however given the request and the desired results if you put C4:H13 and he wants coats well it won't pull the correct results. So he would be forced to change the formula to cut out the data he didn't want to include via coats or the dresses. This is my opinion though.
This probably combines some of the answers above but this is how I interpret the problem.
I moved the word "Dresses" to be in C17 instead of B17 and added Coats to D17.
Enter this ARRAY FORMULA (enter with Ctrl + Shift + Enter) in C18 and fill across and down. This will fill in the values from the chanr for the criteria "Retail Value Inc VAT".
Formula:Please Login or Register to view this content.
I changed the value of the coats so that the differences in the Coats and Dresses would be obvious.
Thank you everyone for fantastic answers and feedback - my apologies as I should have been clearer within data supplied ( maybe different Sell Value for each Category would have helped ! )
I think AlKey solved it - going to try in my master document this morning and I'm sure I'll be able to mark as solved
thanks
Peter
Hi - apologies for late reply - can confirm worked and solved - many many thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks