Return value using multiple criteria from multiple worksheets



I am trying to use lookup values from [Workbook attached] Site and Date columns on the Score worksheet and validate it against data on Sheet4 (pivot table). If both conditions are true, i.e Site=Name and Date=surveyweek, then it should fetch Average of response rate from pivot table (Sheet4) otherwise return 0. I tried using AND with multiple VLOOKUPS but failed and also tried an INDEX/MATCH function but stuck with #REF. Look forward to some guidance from the gurus.




Selected Answer

You need an array formula for this. Here it is.

=IFERROR(INDEX(INDEX(Test,,7),MATCH(1, (INDEX(Test,,1)=$A2)*(INDEX(Test,,4)=TEXT($B2,"0")),0)),"")

Enter it in your 'Score' sheet in cell I2. Since it is an array formula you must confirm it with Ctl+Shift+Enter. Then you can copy and paste down as you do with normal formulas.

Your Pivot table isn't much use for this kind of work because it has all blanks in column A except for the occasional value. Therefore I used the 'DistributionByWeek2' sheet instead. There I created a named range which I called "Test". It comprises columns A to G, from row 2 down. You can call the range whatever you like and define it whichever way best fits your project. Change the range's name in the above formula according to what you created.

In the above formula "INDEX(Test,,1)" specifies the first column of the 'Test' range. Accordingly, "INDEX(Test,,7)" specifies column G (the 7th column). I used this system because I wanted only one named range. If you prefer simpler code you could name a range as "Avg" and refer to it as "Avg" in the above formula, like =IFERROR(INDEX(Avg, MATCH(1, ...". You might replace the other columns by named ranges "Weeks" and "Projects", each of them having a single column only, and therefore not requiring the INDEX function to extract a particular column.

"TEXT($B2,"0")" converts the number you have in B2 into a text string which will match the "=C2&B2" string you create in 'DistributionByWeek2'. Score!B2 is a number. Please bear that in mind when and if you make changes in regard to this detail. Excel sees a big difference between 201715 and "201715" (the latter being a string, the former a number).

The main formula will throw a N/A error if no match is found. This error is caught by the IFERROR function into which the main function is embedded. The final "" in the above formula is the output you will get in case of error. You can insert something else there, like "No data". Just place your text between the quotation marks.

In case of success the value returned will have the format it inherits from the discovered cell. In your test sheet this is an unformatted percentage. You may like to define a specific number of digits and perhaps add the % sign. That is a matter of cell formatting. Any format you apply to Score!I:I will override the format inherited from the source cell.



Thank you so much sir for your help and detailed explaination - worked nicely. Not only did you provide the formula but described each part with much patience. As the proverb goes 'give a man a fish and you feed him for a day, teach him to fish and you feed him for a lifetime'. Thanks again for sharing the knowledge and your time - much appreciated!
deepMonks (rep: 2) Jul 26, '17 at 9:51 pm
Thank you for your kind words. I'm glad I took the trouble to help you. Come and join the community. This is how it works:- You post a question, you get an answer, If you like it you award points, if it helps you you accept it. The latter two are very important because you give your vote both to the expert and his work. This in turn helps others who look for answers.
Then, as Don has pointed out, you don't ask follow-up questions. That is because they will not help any third parties. Perhaps your follow-up is a border case. I couldn't find out because your file didn't download for me.
Please go back and accept the answer. Then see how you wish to handle your follow-up. If I can get at the file I will help you one way or the other.
Variatus (rep: 2354) Jul 26, '17 at 10:18 pm
Add to Discussion


         Please suggest solve the work. Find the attached file.

Thanks and regards




I don' under stant
Pcgksa Jul 25, '17 at 8:00 am
You need to post your question as a new question! Here, you made a reply to someone else's question.

Go to the forum page and click Ask Question and post your question there.
don (rep: 1521) Jul 25, '17 at 9:29 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login