Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and Index function
=IF($B2="","No EIN to Match", INDEX(business,MATCH($B2,EIN,0)))
At first this formula worked fine and then it just kept have more and more problems what is wrong? Column A2:A34 has the above formula. Column B2:B34 is EINs (employer Identification Numbers. A38:A70 has a header "Business Name" B38:B70 has a header "EIN" Column A Column B Column C Column D A2:A34 B2:B34 C2:C34 D2:D34 Business name Employer Taxpayer Electronic transfer number (EFT) Identification Type Number Number Column A Column B Business Name EIN I copy from another program that has Employer Identification Numbers (EIN) and other information into Column B2:B34 and it paste across the sheet then column B2:B34 is to take the number that is in its column B and look lower in column B in a Name Range called EIN (B38:B70) and the look left to Column A (A38:A70) for the business name which is in a Named Range business. then take the business name and transfer it up to column A (A2:A34). when I transfer the information into the excel sheet it is to give me the name without me manually comparing EINs to find the Business name. then I take this information and copy some of it onto sheet 2 and then take that information and paste it in word with label merge (sub part of mail merge) and then print the labels. Gary Reger |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and Index function
Hi Gary,
apart from saying that you keep having more and more problems, you haven't described what type of problems you are having, so it is difficult to advise you what is wrong. Is the formula producing error messages like #NUM or #VALUE or #N/A ? Are you sure that your EIN numbers are in the same format in boths parts of your tables? Please post back with some more details. Pete On Feb 15, 6:56 pm, "Gary Reger" wrote: =IF($B2="","No EIN to Match", INDEX(business,MATCH($B2,EIN,0))) At first this formula worked fine and then it just kept have more and more problems what is wrong? Column A2:A34 has the above formula. Column B2:B34 is EINs (employer Identification Numbers. A38:A70 has a header "Business Name" B38:B70 has a header "EIN" Column A Column B Column C Column D A2:A34 B2:B34 C2:C34 D2:D34 Business name Employer Taxpayer Electronic transfer number (EFT) Identification Type Number Number Column A Column B Business Name EIN I copy from another program that has Employer Identification Numbers (EIN) and other information into Column B2:B34 and it paste across the sheet then column B2:B34 is to take the number that is in its column B and look lower in column B in a Name Range called EIN (B38:B70) and the look left to Column A (A38:A70) for the business name which is in a Named Range business. then take the business name and transfer it up to column A (A2:A34). when I transfer the information into the excel sheet it is to give me the name without me manually comparing EINs to find the Business name. then I take this information and copy some of it onto sheet 2 and then take that information and paste it in word with label merge (sub part of mail merge) and then print the labels. Gary Reger |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and Index function
EIN in both part of the table are the same. I have tried text, numbers,
custom. at first if worked fine. then a couple of tries later 2 or 3 out of 15 would not work but the rest around them would, some way i fixed them with a lot of tinkering and not sure what the trick was that worked. then the next a few more would work and some more tinkering and they would work. now none of them will work. the error i get every time is #NA. thanks for your response Gary "Pete_UK" wrote in message ups.com... Hi Gary, apart from saying that you keep having more and more problems, you haven't described what type of problems you are having, so it is difficult to advise you what is wrong. Is the formula producing error messages like #NUM or #VALUE or #N/A ? Are you sure that your EIN numbers are in the same format in boths parts of your tables? Please post back with some more details. Pete On Feb 15, 6:56 pm, "Gary Reger" wrote: =IF($B2="","No EIN to Match", INDEX(business,MATCH($B2,EIN,0))) At first this formula worked fine and then it just kept have more and more problems what is wrong? Column A2:A34 has the above formula. Column B2:B34 is EINs (employer Identification Numbers. A38:A70 has a header "Business Name" B38:B70 has a header "EIN" Column A Column B Column C Column D A2:A34 B2:B34 C2:C34 D2:D34 Business name Employer Taxpayer Electronic transfer number (EFT) Identification Type Number Number Column A Column B Business Name EIN I copy from another program that has Employer Identification Numbers (EIN) and other information into Column B2:B34 and it paste across the sheet then column B2:B34 is to take the number that is in its column B and look lower in column B in a Name Range called EIN (B38:B70) and the look left to Column A (A38:A70) for the business name which is in a Named Range business. then take the business name and transfer it up to column A (A2:A34). when I transfer the information into the excel sheet it is to give me the name without me manually comparing EINs to find the Business name. then I take this information and copy some of it onto sheet 2 and then take that information and paste it in word with label merge (sub part of mail merge) and then print the labels. Gary Reger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
index match function help me | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
index / match function | Excel Worksheet Functions | |||
Need help on index and match function | Excel Worksheet Functions |