# Lookup and Match

##### New Member
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
88.6 KB · Views: 5
• Dashboard.JPG
35.7 KB · Views: 6

##### New Member
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
74.1 KB · Views: 2
• Capture 8.JPG
173.1 KB · Views: 2

### 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
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.

##### New Member
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
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.

##### New Member
Thank You for all your help.. it's working now

Replies
1
Views
85
Replies
6
Views
62
Replies
1
Views
93
Replies
3
Views
354
Replies
2
Views
77