200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello,
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.
Thanks,
D
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.
Hello
Please suggest solve the work. Find the attached file.
Thanks and regards
C.Purusothaman