Index/Match at the battle of Multiple Tables

[Dalek_Rider]

Hello,

First thank you for taking the time to look at this post and any help/insight that you may be able to offer.

So here is what I have:

I have a workbook with 2 sheets: Template and Inventory. I want to be able to input a unique reference ID into cell on the template sheet that populates additional cells in the same row

My understanding is that on the template worksheet, the Columns labeled Name and Unit Price would have to perform checks or a logical "if" this equals this then do this. So a cell in the name column would check a cell in the same row under column "ItemNumber" and look at the value, then check all the tables on the inventory sheet under column Reference ID then if finding a match would return the name that matchs on the inventort sheet. My initial thoughts were I needed to use something like LOOKUP but I think I need something along an index match argument. Lookup failed me so I tried the index match after checking a few sections through out this site and others.

I can get this to work, but it only works for one table, when I try to add multiple tables to the argument excel starts to have a nervous break down

Working: =INDEX(cdTable[Name],MATCH(Template!D7,cdTable[Reference ID],0))

Not Working: =INDEX(Inventory!B8:B15,Inventory!B19:B39,MATCH(Template!D7,Inventory!C8:C15,Inventory!C19:C39,0))

What are my options here? should be Be looking for an different formula? or am I somewhat forced to just list all the inventory under one table? That seems to be the easiest solutions but not the most organized one.

I have made a separate file with the worksheets in question but the upload does not seem to be picking them up. I will have to look back at the terms/conditions of posting again since it has been quite some time.


**** I Suppose I should mention that B8:B15 is from one table and B19:B39 is from another table ****

 

[Dalek_Rider]

Hopefully this helps, otherwise I can link to a google drive file. I know that the formula seems broken in google drive but really I just want it to work in Office

 

[Chihiro]

You can use IFERROR to look through multiple tables.

See attached

 

[Dalek_Rider]

Thanks!, that does exactly what I need it to. That is a heck of a long string of IFERRORS but it works lol. I had seen a few posts about IFERROR and Excel seemed to shove it in my face at one point while working on this. I think I need to break down and just go through a few classes. I've managed to learn enough as I go, from this place and that place to be anywhere from decent to dangerous to a speadsheet.

Thanks again :D