I am trying to lookup or index only part of a cell, but I keep getting an #N/A error.

When I use VLOOKUP, I want my lookup_value to be only the LEFT 4 characters.
Alternatively, when I use INDEX/MATCH/MATCH, I want my first MATCH look_up value to be only the LEFT 4 characters.

I have tried this two ways with both functions: using the LEFT function directly into the other formulas,
=INDEX('[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$30,MATCH(LEFT(C2,4),'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$A$30,0),MATCH(F2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$4,0))
and also creating a column to return the LEFT 4 characters and using that column in my formula.
*C2 example: 5562-3327* *Formula in D2 =LEFT(C2,4)* =INDEX('[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$30,MATCH(D2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$A$30,0),MATCH(F2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$4,0))

Any way around this? When I physically type in the text (example: 5562) and use that cell in my formula, it works, so the functions are working properly.
=INDEX('[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$30,MATCH(D2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$A$30,0),MATCH(F2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$4,0))

But when I try use to use LEFT function, it gives me an #N/A error, when using either VLOOKUP or INDEX/MATCH/MATCH functions