Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |