Index/Match and Concat using Array formula without CSE

[mpost54]

On 'Invoice' worksheet solve col 'E18:E26' based on cell C14 content which refers to Table 3, Table 4 or Table 5 on 'variables' worksheet

Cell C14 is referenced from the 'Principal_Contractor' worksheet.

My problem is getting unique information for the index/match function to give me the correct 'Unit Price' in col E on the 'Invoice' worksheet

The attached file shows an error E23 on 'Invoice' worksheet which should read $50

What I am saying here if you look at 'Table 3' on the 'Variables' worksheet cells in columns A,B or C can contain the same information - so I assume it needs to be concatenated in some way to make it unique

[Gregb11]

One option is this:
Add another column to each Table on "Variables" sheet that concatenates Service and Discount (you can hide the column).
=[@Service]&[@Discount]

I then changed the formula in column E to:

=IFERROR(INDEX(Table3[DP Inc Con],MATCH([@Description]&[@Disc],Table3[Column1],0)),"")

Attached is changed version (I only added the add'l column to the "Table3" on the "Variables" sheet.

[mpost54]

Hi Greg
Thanks for your reply
Your formula works well for table 3 - but if I change c14 on the 'Invoice' worksheet to '2' the formula needs to use table 4 and so on,
So the formula needs to be dynamic to include tables 3, 4 & 5
Mick

[Gregb11]

Would you be open to changing your tables? In the attached I put all your Variables into 1 table instead of 3 and added a column for the Contractor ID. I then added (concatenated) the Contractor ID to the new column I added before, and then changed the formula on the invoice tab to:
=IFERROR(INDEX(Table3[DP Inc Con],MATCH($C$14&[@Description]&[@Disc],Table3[Column1],0)),"")

Now when you change the Contractor ID to 2 or 3, the values will change.

[mpost54]

Thanks great work - I did not see that solution coming.
Maybe I am too old for this stuff

[Gregb11]

Ha! I'm probably older than you
Glad I could help - good luck.