VLOOKUP Formula -- Adding a Unique Condition

[meppwc]

I have a spreadsheet that consists of just one worksheet (AvailableItems).
Column A is a list of parts that I stock (alpha-numeric-special characters)
Column D is a list of available parts (alpha-numeric-special characters)
Cell C2 contains the following formula - =IFERROR(VLOOKUP(A2,D:D,1,FALSE),""), and this formula is copied all the way down to C43000
The formula works correctly.

But if the part number being searched for exists in column D, but is preceded with “D-“ I want that to be seen as a find. Is there any way to adapt this formula to take that condition into consideration?
Example:
123456 from column A is being searched on in column D
123456 exists in column D so it is a “find” or “hit” so the appropriate cell in column C displays the part number
D-123456 also exists in column D so for me it too is considered a “find” or “hit” so I would like that part number to be displayed as well.

 

[Aladin Akyurek]

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(A2,D:D),D:D),"")

Better not to reference the whole column though. Instead use the exact range.

 

[meppwc]

Aladin
Thank you so much assistance. I took your advice and used exact range which helps with resources. I was just getting ready to ask you what (LOOKUP(9.99999999999999E+307 means/does.........and found that you have provided an explanation when you assisted other users with the same issue. Thank you so much for the help. From the results that I have checked so far it appears that it is working correctly. But I still have 38,000 left to validate (LOL).

 

[Aladin Akyurek]

@ meppwc

You are welcome. Thanks for the update.

 

[meppwc]

Aladin..........there is one other question I would like to ask you. Looking at the formula that you have provided, I am trying to figure out how it knows to pickup those cells that contain the part numbers that are preceded with "D-" I am just curious and would like to understand.

 

[Aladin Akyurek]

Aladin..........there is one other question I would like to ask you. Looking at the formula that you have provided, I am trying to figure out how it knows to pickup those cells that contain the part numbers that are preceded with "D-" I am just curious and would like to understand.

SEARCH behaves as if it looks for A2 with a * wildcard around it, that is, "*"&A2&"*".

If A2 = 123456, it looks for * 123456*. This effectively means: 123456, D-123456, etc.

 

[meppwc]

makes perfect sense...........and ty again