Match and Index function

[Gary Reger]

=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
D234
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

 

[Pete_UK]

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

 

[Gary Reger]

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