Need help with a search function when returning multiple fields

So I have been working on a workbook that has a search function to return multiple fields. I was wondering if there was a way to differentiate between separate fields using this function if my search returns more than 1 result. Currently it just grabs the information from the first field that is found.

The example I am posting shows 2 exact same fields (D17 and D18). The search function searches entire D column but if you search for one that has multiple entries, there is currently no way to select or differentiate which one I am actually referring to.

What I am wondering is, is there a formula that would make it so I could differentiate manually? or would this be something that I need to code using the VBA?

Thank you in advance!

Using Named ranges instead of entire columns*
Manufacturer_Item_ID =Sheet1!$D$1:$D$18
Manufacturer_Name =Sheet1!$C$1:$C$18
RCM_Item_ID_Barcode =Sheet1!$E$1:$E$18

*can be made dynamic...

H13:
count the number of matches for G13; enter values >1 only

Please Login or Register  to view this content.

If multiple matches, then I13 accepts a value from k=1 to #matches. This number is used by SMALL() to return the k^th item in the match array.
G16:

Please Login or Register  to view this content.

G17:

Please Login or Register  to view this content.

Hey Proton, thanks for the response! I do get that the search can be made more dynamic using exact cell conditions but herein lies the problem. The actual workbook that these formulas will be going in are roughly 51,000 rows deep and will be constantly adding new cells(hence why I had it searching the entire column) I was just trying to find a solution for if it came back with multiple results without having to re-edit the formula every time a new entry is added.

"...the search can be made more dynamic using exact cell conditions...", no, when talking about Excel formulas that's not what dynamic means.

A dynamic named range is one where the rows/columns are adjusted by Excel without the need for editing due to changes.

One of the named ranges I gave you was:
Manufacturer_Item_ID =Sheet1!$D$1:$D$18

To make Manufacturer_Item_ID dynamic (i.e., self-adjusting), you would use:

Please Login or Register  to view this content.

and you will never have to edit/modify it with changes in number of rows in the data.

The solution I gave you tells you when/if there are multiple occurrences of the search value and lets you choose which you want. Did you try it (I don't know from your last post)?

Ok so now they changed their mind and want to do it the first way... When I copied the formula's into my wb it's not returning the correct value for G17 and also the value for G15 disappears . Please help.

Edit: Also if there are no duplicates I still need it to return the pertinent data. Thank you.

If only one item is found, cell I13 needs to bel cleared. I added ad data validation to I13.

Specific example....

In the second wkbk I posted, I was returning data from Column B to cell G15. On the one you just posted, it is returning column A. However, it looks like you forgot to commit the *array* formula with Ctrl+shift+enter. When I did, it returned ADH0005 from row 13 vs ADH0007 from row 15.

You're welcome