Hlookup issue, duplicates

[pjhdude]

Hello all, I am having an issue where my HLOOKUP is finding duplicate values associated to different peoples names?
So my HLOOKUP is find a cell value and input a name tide to it, but there are two people that have that number and it is just showing the first name twice??

Please help.

Thanks

[sandy666]

Attach a sample Excel workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[pjhdude]

I have attached a sample. I need the formula to know to skip and fill in the second name if there are duplicates.

Thanks

[sandy666]

you can do that with PowerQuery (Get&Transform)

add more data to the horizontal table then refresh green table
see attached file:

[PietBom]

My solution is without PowerQuery, using array-formulas

[pjhdude]

Can you explain where the formulas numbers come from and what they mean? I tried inputting that into my spreadsheet but it came up N/A.
Not sure if it is because of row number or???
Thanks

[PietBom]

Can you explain where the formulas numbers come from and what they mean? I tried inputting that into my spreadsheet but it came up N/A.
Not sure if it is because of row number or???
Thanks

Normal formulas you finish with Enter
Array formulas must be finished with Ctrl+Shift+Enter
Then the formula will look like {=formula}

[leelnich]

Hi all - Here's a version with regular formulas. In B2, copied down:

Please Login or Register  to view this content.

In A2, copied down:

Please Login or Register  to view this content.

The AGGREGATE(function_num, behavior_type, array, k) function is configured to mimic the SMALL function and ignore errors. It acts as a filter and returns the kth smallest column that contains a match to the value in column B. This is then converted to a list position for use with the INDEX function.

[pjhdude]

Thanks leelnich, worked great!