VLOOKUP issue with wildcard parameter for partial matches in number/letter combinations

[BrooksProctor]

Hello, so I'm using Microsoft Excel on Office 365 and I'm using the VLOOKUP function and having some issues.

Here is the current state of my formula I'm trying to troubleshoot:
=VLOOKUP(C3&"*",A:B,2,FALSE)

Column A is filled with identification keys composed of letters and numbers while column B contains the corresponding measurement numbers. Now while this function does technically work, it is exclusively working for cells that match the exact value of the identification keys in column A. My desired output was for me to be able to write in a new cell (in this case C3) an ID key, excel would see that the front part of the key matched an existing one, and then in the next cell it would output the corresponding measurement. As I mentioned, it's not working even though I used the &"*" wildcard adjustment. If I write a single space, letter, or number after the ID that already matches then the output will be #N/A.

I've looked through so many tutorials, asked AI assistants, and tried a bunch of workarounds like combining MATCH and INDEX but nothing is working. I swear this seems so simple, yet the wildcard just seems to malfunction. Any help would be much appreciated.

 

[Fluff]

Can you post some sample data that shows the problem, as that should work.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[BrooksProctor]

A (ID code)	B (Measurement)	C (Manual Input)	D (Result)
1​	351 DBZ	3.5	401 DBZ	=VLOOKUP(C1&"*", A:B, 2, FALSE)
2​	401 DBZ	4.19	401 DBZ E	#N/A
3​	451 DBZ	4.5
...​	...	...

Apologies, I'm unable to use XL2BB but here is a sample from my sheet and all relevant formulas. D2 is identical to D1 except the formula is indexed one down =VLOOKUP(C2&"*", A:B, 2, FALSE). D1 on my sheet yields the correct value of 4.19. I hope the table I inserted is sufficient, apologies if this makes it more difficult for you to troubleshoot but I really appreciate it.

 

[Fluff]

The value in D2 does not exist in those three rows, which is why you get #N/A

 

[BrooksProctor]

*Corrected column headers from last message

	A (ID code)	B (Measurement)	C (Manual Input)	D (Result)
1	351 DBZ	3.5	401 DBZ	=VLOOKUP(C1&"*", A:B, 2, FALSE)
2	401 DBZ	4.19	401 DBZ E	#N/A
3	451 DBZ	4.5
...	...	...

Apologies, I'm unable to use XL2BB but here is a sample from my sheet and all relevant formulas. D2 is identical to D1 except the formula is indexed one down =VLOOKUP(C2&"*", A:B, 2, FALSE). D1 on my sheet yields the correct value of 4.19. I hope the table I inserted is sufficient, apologies if this makes it more difficult for you to troubleshoot but I really appreciate it.

 

[Fluff]

The columns are irrelevant, you stall don't have a value of "401 DBZ E" in col A

 

[BrooksProctor]

The columns are irrelevant, you stall don't have a value of "401 DBZ E" in col A

No I know that, the whole point of this is that I'm trying to search for a partial match so long as the first part matches. The wildcard modification is meant to allow other values to exist without excluding results so long as just the front matches right? Like doing "*"& before or &"*" after a text parameter is a thing correct? I've seen so many tutorials explaining this but it won't work on mine.

 

[Fluff]

Adding the * at the end simply means that it looks for anything that starts with the exact string "401 DBZ E"

 

[FormR]

Hi, it looks like you want the partial match the other way around, i.e. that the lookup array partially matches the lookup value. If so, here is one option you can try:

Edit, if all the ID codes in column A are the same length (like the sample data) then this can be simplified greatly.

Book3
	A	B	C	D
1	A (ID code)	B (Measurement)	C (Manual Input)	D (Result)
2	351 DBZ	3.5	401 DBZ	4.19
3	401 DBZ	4.19	401 DBZ E	4.19
4	451 DBZ	4.5
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=XLOOKUP(TRUE,LEFT(C2,LEN($A$2:$A$4))=$A$2:$A$4,$B$2:$B$4)

 

[BrooksProctor]

Hi, it looks like you want the partial match the other way around, i.e. that the lookup array partially matches the lookup value. If so, here is one option you can try:

Edit, if all the ID codes in column A are the same length (like the sample data) then this can be simplified greatly.

Book3
	A	B	C	D
1	A (ID code)	B (Measurement)	C (Manual Input)	D (Result)
2	351 DBZ	3.5	401 DBZ	4.19
3	401 DBZ	4.19	401 DBZ E	4.19
4	451 DBZ	4.5
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=XLOOKUP(TRUE,LEFT(C2,LEN($A$2:$A$4))=$A$2:$A$4,$B$2:$B$4)

Thank you so much FormR, I will try this and let you know how it goes.