Well, we can use VLOOKUP with Partial Match and that ***almost*** works (see:

https://exceljet.net/formula/partial-match-with-vlookup),

i.e. place this formula in D1.

Code:

`=IFERROR(VLOOKUP("*" & D1 & "*",A:C,3,0),"")`

Where this runs into problems is if you tried to look up a value like "C4".

In your example, "C4" is not listed anywhere. However, you have values like "C42", "C43", etc.

When it looks for "C4" as part of a string, it finds lots of matches, though there really isn't a perfect "C4" only match.

So you are going to have "false matches".

The only ways I can think to prevent this from happening is to:

- Change the format of your data so that all values are of the same length. So, if there are all to be 3 characters, you should have "C01" instead of "C1", etc.

or

- Fix your data table so that you only have one value per row in column A. We could ever write a macro to expand that out. If you do that, then a simply VLOOKUP will do what you want.