Lookup and Match

Gopal Pradhan

New Member
Joined
Sep 7, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
I am performing a NPS survey and with in that given data i have my month and my survey results horizontally. Below is an example

0 1 2 3 4 5 6 7 8 9 10
Jan
Feb
March
April

So i am here calculating the NPS calculation month wise. So end result am looking at is, for January month, of the responses how many 0, 1 ,2 3 (looking for count).

The data in my other sheet i am trying to lookup and match is something like below : ( There are total 6 questions out of which the responses are received )

Q1 Q2 Q3 Q4 Q5 A6
January 0 4 10 9 3 10
February 2 5 8 9 2 7
February 0 4 10 9 4 10
February 0 4 10 9 6 10
March 0 4 10 9 9 10
March 0 4 10 9 10 10

So my end dashboard result would be, in a given month how many 0 to 10 i have received separately
 

Attachments

  • Dump.JPG
    Dump.JPG
    88.6 KB · Views: 5
  • Dashboard.JPG
    Dashboard.JPG
    35.7 KB · Views: 6

Gopal Pradhan

New Member
Joined
Sep 7, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
The format in both the sheets are the same. Have used the Trim formula too. Data matched for April and have discrepancies for May.
In Raw Dump there are 4 rows for May and for June there are approx 20 rows with 20 responses. Attached is the screenshot for the NPS and the RawDump
 

Attachments

  • Capture 7.JPG
    Capture 7.JPG
    74.1 KB · Views: 2
  • Capture 8.JPG
    Capture 8.JPG
    173.1 KB · Views: 2

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,601
Office Version
  1. 365
Platform
  1. Windows
Because you haven't edited the formula correctly. As I said before, there were a lot of assumptions in the formula to make up for missing information, which was why I added this to the reply so that you could edit the formula correctly.
Note that I have marked 2 ranges in bold, the first one must point to the month names in the 'Dump' image, the second one must point to the scores in the 'Dump' image.

=SUMPRODUCT(('NPS Scoring Trend Month Wise '!$A$2:$A$10=$A2)*('NPS Scoring Trend Month Wise '!$B$2:$G$10=B$1))
Last guess based on the information provided. Again this must go into B2 then you drag it to fill the sheet as necessary.

=SUMPRODUCT(('RawDump'!$A$2:$A$22=$A2)*('RawDump'!$B$2:$G$22=B$1))

As I can't see the end of your data you will have to change the parts in red to match the last row and last column used.

The row number must be the same in both parts.

Do not change anything that is not red.
 

Gopal Pradhan

New Member
Joined
Sep 7, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Thank you for the response.. I am getting the values but they seem to be incorrect...
is there any way that i can share the excel to you and you can have a look ?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,601
Office Version
  1. 365
Platform
  1. Windows
One thing to take into consideration, empty cells will be counted as having a value of 0

You can either use the XL2BB add in to post a full capture to the forum (see link below) or you can upload your file to a sharing service such as dropbox and post a link here.


I'll have a look at it as soon as I get chance, but most likely that will not be until tomorrow.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,808
Messages
5,574,434
Members
412,592
Latest member
moonsugar
Top