Help With Formula Showing Value

[mbrown279]

Hi All,


I am currently using an index/match formula to bring score data from one table to another. The problem I am having is that it is assigning a zero value in fields that have no data. This is causing an issues when I am pivoting out the column using that data.


We have two set of scores "Current Score" and "Updated Score" The updated score should reflect the same information as the current score unless it was changed. If the field is blank it should remain blank in the updated score. Below the formula that I am currently using.

=IF(ISNA(INDEX(Updated[NPS],MATCH([Conversation ID],Updated[Conversation ID],0))),"",INDEX(Updated[NPS],MATCH([Conversation ID],Updated[Conversation ID],0)))

When I do a count comparing number of current scores to updated there is a huge variance. It should be the same count.

 

[cyrilbrd]

Hi, what version of excel are you using, this will be helpful for other readers.

 

[cyrilbrd]

If Excel 2007 and above try something like:

=IFERROR(IF(INDEX(Updated[NPS],MATCH([Conversation ID],Updated[Conversation ID],0))=0,"",INDEX(Updated[NPS],MATCH([Conversation ID],Updated[Conversation ID],0))),"")

 

[mbrown279]

Thank you so much this worked perfectly. I am used O365 by the way.

If Excel 2007 and above try something like:

=IFERROR(IF(INDEX(Updated[NPS],MATCH([Conversation ID],Updated[Conversation ID],0))=0,"",INDEX(Updated[NPS],MATCH([Conversation ID],Updated[Conversation ID],0))),"")

 

[cyrilbrd]

Thank you so much this worked perfectly. I am used O365 by the way.

Glad it worked for you, and thanks for your feedback.