INDEX/MATCH returning value on row BEFORE the right one

[megera716]

I need to do a lookup on multiple criteria. I put this formula together and was happy to see it worked but just discovered it's returning the incorrect value.

Here is my formula -- and yes, I am doing Ctrl+Shift+Enter to do this as an array.
INDEX(Table3[[#All],[Name]:[AccessID]],MATCH(1,([@[Toolbox Name]]=Table3[Name])*([@[TB Org Name]]=Table3[ProviderName]),0),17)

Table3 is huge -- 200,000 row by 17 columns (beginning with Name, ending with Access ID).

Then I have my own table that's 1,600 rows by 15 columns that I'm trying to look up info on the people in there in Table3.
[TABLE="width: 861"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Toolbox Name[/TD]
[TD]Toolbox Org Name[/TD]
[TD]ID #[/TD]
[/TR]
[TR]
[TD]David Jones
John Smith[/TD]
[TD]Smith Hospital
Main Street Hospital[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]State Street Hospital[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

John Smith is located in Table3, and has his own unique ID in there that ties him and his org together, but I need to get that into my new table based on both Name AND Org Name criteria. When I use the above formula, it returns the row ABOVE the matching one, even though none of that info is the same.

For example, if I'm trying to look up John Smith, it returns the ID # on David Jones row even though neither the name nor the org name matches.

Sorry if this is confusing. I'll be happy to clarify as needed!

 

[Ron Coderre]

It appears that the first referenced range in your INDEX function refers to the entire table column (including the heading), but the MATCH function refers to only the data body range. Consequently, the incorrrect value would be returned.

Try changing
this: INDEX(Table3[[#All],[Name]:[AccessID]],
to this: INDEX(Table3[[Name]:[AccessID]],

Does that help?

 

[megera716]

It appears that the first referenced range in your INDEX function refers to the entire table column (including the heading), but the MATCH function refers to only the data body range. Consequently, the incorrrect value would be returned.

Try changing
this: INDEX(Table3[[#All],[Name]:[AccessID]],
to this: INDEX(Table3[[Name]:[AccessID]],

Does that help?

YES!

THANK YOU, THANK YOU! Such a simple fix, but I couldn't have figured it out! Can I give you internet points or hugs or my undying gratitude (you can imagine that with 200K rows, this is hardly the project for manual entry)?

​

 

[Ron Coderre]

Thanks for the kind words...I'm glad your workbook is up and running now.