Index match formula not matching properly.

[vba317]

I started with a vlookup but moved to an index match. I am trying to match two columns 1 column has an ID in it the other column has a ^ with an ID. If I put the formula of the cells I get a match but when I use the index match it won't match. I am comparing value from one sheet with values from another sheet. The second sheet is named EPIC_Providers. Any help would be appreciated.


The cell comparison formulas:

Cell AR2 Cell AS2 Currently true
=(SUBSTITUTE(AN2|"^"|"")) =(EPIC_Providers!B306 ) =AR2=AS2



=INDEX(EPIC_Providers!B:C| MATCH(SUBSTITUTE(AN2|"^"|"")|AN2:AN51|0))
The result is #N/A

 

[Chrisdontm]

=INDEX(EPIC_Providers!B:C| MATCH(SUBSTITUTE(AN2|"^"|"")|AN2:AN51|0))
The result is #N/A[/QUOTE]


There appears to be a couple of things not lined up...

I use a comma and not a bar: " | " between the different parts of a formula.

In the MATCH formula you are telling it to look up AN2 without the carrot ( ^ ), and then telling it to look in the same column where everything in that column has a carrot in it, so it will never find a match. ( You probably mean for it to look in the other tab, column "B" )

Once you get that straight and you get it to tell you a row number, which is what MATCH will return for you, then,
In the INDEX formula you are telling it to look in two columns, but you are not telling it which column you want.
( You are probably wanting column 2, which is column "C" of your range )


So my guess of how you want the formula constructed would go like this if the ID is a text;

=INDEX(EPIC_Providers!B:C, MATCH(SUBSTITUTE(AN2,"^",""),EPIC_Providers!$B:$B,0),2)


If the ID is a number, then I would use this;

=INDEX(EPIC_Providers!B:C, MATCH(SUBSTITUTE(AN2,"^","")*1,EPIC_Providers!$B:$B,0),2)

 

[vba317]

=INDEX(EPIC_Providers!B:C| MATCH(SUBSTITUTE(AN2|"^"|"")|AN2:AN51|0))
The result is #N/A

There appears to be a couple of things not lined up...

I use a comma and not a bar: " | " between the different parts of a formula.

In the MATCH formula you are telling it to look up AN2 without the carrot ( ^ ), and then telling it to look in the same column where everything in that column has a carrot in it, so it will never find a match. ( You probably mean for it to look in the other tab, column "B" )

Once you get that straight and you get it to tell you a row number, which is what MATCH will return for you, then,
In the INDEX formula you are telling it to look in two columns, but you are not telling it which column you want.
( You are probably wanting column 2, which is column "C" of your range )


So my guess of how you want the formula constructed would go like this if the ID is a text;

=INDEX(EPIC_Providers!B:C, MATCH(SUBSTITUTE(AN2,"^",""),EPIC_Providers!$B:$B,0),2)


If the ID is a number, then I would use this;

=INDEX(EPIC_Providers!B:C, MATCH(SUBSTITUTE(AN2,"^","")*1,EPIC_Providers!$B:$B,0),2)[/QUOTE]



=INDEX(EPIC_Providers!B:C, MATCH(SUBSTITUTE(AN2,"^",""),EPIC_Providers!$B:$B,0),2)

Worked great thanks! I don't know why I have to change the commas to |, if I don't I get a formula error.