Hi,
I am having trouble setting up an index and match formula.
On the “Avg Anaplan” tab I need the formula here to pull data from the “Hyg” tab.
The matches are:
Match column B on the “Avg Anaplan” with Row 39 on the “Hyg” tab
Match row 16 on the “Avg Anaplan” with Column B on the “Hyg” tab
I have attempted the formula but it doesn’t work
Can anyone help please?
Paul i am not a Hakker. I can not brake the code!!The sheet is protected by code!
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Your file is protected with an password.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Sorry all that tried to open it! I am an idiot!! :O
Paul the "numbers" in column B(180,181....) in "Avg Anaplan” tab, are actually text look like number. If you replace these with real numbers your formula works.
I have no time to cjeck if gives correct results but i think that it does!
Paul the "numbers" in column B(180,181....) in "Avg Anaplan” tab, are actually text look like number. If you replace these with real numbers your formula works.
I have no time to check if gives correct results but i think that it does!
You were right about the number being stored as text!! However... I notice that my matches are not unique - this was an oversight!
I need the sum of the matches - is this SUMPRODUCT?
Maybe if you included a few sample answers, it would help?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
In D17
=SUMIF(Hyg!$B$41:$B$72,D$16,INDEX(Hyg!$E$41:$N$72,,MATCH($B17,Hyg!$E$39:$M$39,0)))
Copy down and across
Life's a spreadsheet, Excel!
Say thanks, Click *
I couldnt log in sooner to reply! That formula worked for the model supplied! I have never seen and index and match formula within a SUMIF.
I have tried to apply the same theory to my only other model but I cant get it to work - what am I doing wrong (its sooooo frustrating)
The numbers in the model are meaningless e.g. nothign is reported for branch 111 - this should be 19 (as reported on the "Budgeted FY14" tab
Your ranges are inconsistent
In C39
=SUMIF('Budget FY14'!$B$5:$B$36,C$38,INDEX('Budget FY14'!$F$5:$AL$36,,MATCH($A39,'Budget FY14'!$F$2:$AL$2,0)))
Thanks ACE! I need to do a bit more learning it seems!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks