Index and match
 

I have a spreadsheet with 2 pivot tables side by side. One pivot table is
created using data from cash application and the other pivot table is from
bank transactions. The two pivot tables are compared for missing data either
in cash application or in bank. The data is the dollar amount obviously. I
had used the formula =IF(COUNTIF(F8,"*Total"),"
",INDEX(GLCC,MATCH(H8,GLCC,0),0)). GLCC is a name for a defined range and I
wanted a blank if matching title has "Total". The formula mostly works but
in lots of cases #N/A is displayed even though there is an exact match. If
necessary I am willing to send the data as an attachment. I need help please.

Index and match
 

Try this amendment which makes it an all text comparison between the lookup
values in H8 down, and the reference col range values (GLCC)
It should yield better results, just normal ENTER to confirm:
=IF(COUNTIF(F8,"*Total"),"",INDEX(GLCC,MATCH(H8&"" ,INDEX(GLCC&"",),0)))
Any joy? celebrate it, thump the YES below
--
Max
Singapore
---
"Krish" wrote:
I have a spreadsheet with 2 pivot tables side by side. One pivot table is
created using data from cash application and the other pivot table is from
bank transactions. The two pivot tables are compared for missing data either
in cash application or in bank. The data is the dollar amount obviously. I
had used the formula =IF(COUNTIF(F8,"*Total"),"
",INDEX(GLCC,MATCH(H8,GLCC,0),0)). GLCC is a name for a defined range and I
wanted a blank if matching title has "Total". The formula mostly works but
in lots of cases #N/A is displayed even though there is an exact match. If
necessary I am willing to send the data as an attachment. I need help please.

Index and match
 

Max:
You are awesome. All the struggle I was facing went away. Your suggestion
really saved me a lot of frustrations. What does really &" " help to
accomplish?
"Max" wrote:

Try this amendment which makes it an all text comparison between the lookup
values in H8 down, and the reference col range values (GLCC)
It should yield better results, just normal ENTER to confirm:
=IF(COUNTIF(F8,"*Total"),"",INDEX(GLCC,MATCH(H8&"" ,INDEX(GLCC&"",),0)))
Any joy? celebrate it, thump the YES below
--
Max
Singapore
---
"Krish" wrote:
I have a spreadsheet with 2 pivot tables side by side. One pivot table is
created using data from cash application and the other pivot table is from
bank transactions. The two pivot tables are compared for missing data either
in cash application or in bank. The data is the dollar amount obviously. I
had used the formula =IF(COUNTIF(F8,"*Total"),"
",INDEX(GLCC,MATCH(H8,GLCC,0),0)). GLCC is a name for a defined range and I
wanted a blank if matching title has "Total". The formula mostly works but
in lots of cases #N/A is displayed even though there is an exact match. If
necessary I am willing to send the data as an attachment. I need help please.

Index and match
 

Welcome, glad it turned out fine
What does really &" " help to accomplish?
It's actually: &"" (there's no space between the double quotes). It converts
real numbers to text numbers for consistent matching w/o impacting existing
text numbers. Trust that clarifies. Please hit the YES button below to
celebrate your success.
--
Max
Singapore
---
"Krish" wrote:
Max:
You are awesome. All the struggle I was facing went away. Your suggestion
really saved me a lot of frustrations. What does really &" " help to
accomplish?