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.