Let's look first at why the function you've posted isn't working. VLOOKUP needs a two-dimensional data range to work with. It looks for your search_key (param 1) in the first column of range specified (has to be the first column - an unfortunate limitation of VLOOKUP), and returns a value from the matching row. The column returned is specified by param 3, index, essentially 'nth column in my range' (so 1 = your search key).
To get around the first column limitation, you could create an extra column with the data you want to return that is to the right of your search_key column. But it's messy, and looking at your data it won't help anyway.
That's because VLOOKUP only ever returns one value. If you have multiple matches for the search_key, it only gives you the first one it finds. I see that you have a few sizes of product with multiple part numbers, so we're going to need a different function.
I'd suggest FILTER for this job. What FILTER does is take a 2D data range, allow you to filter it down based on any column or row within that range, and return all the matching data - it actually fills neighbouring cells if there's enough data. I always think it's a bit weird to think of a function in a cell that actually acts on other cells, but that's how it works.
So what we could do is (in cell E23 ONLY):
filter(Data!A3:B2000,Data!B2:B2000 = E21)
To break that down, look in all of Data for rows where the value of column B matches E21.
Problem is that will return too many columns, and potentially too many rows. If there's data in the neighbouring cells that Sheets is going to put data into, the function will just fail. So we can use the ARRAY_CONSTRAIN function to limit the result of the filter:
=iferror(array_constrain(filter(Data!A3:B2000,Data!B3:B2000 = E21),4,1))
Fortuitously the data we want is in the left column of the range, so we just have to limit it down to one column (last param), and four rows (as you only have four rows available - you could add more if you like).
I've also added an IFERROR to blank it out if there are no results. It's not strictly what it does, but it's much cleaner than using an IF function to check ISNA, and it does the job.
You don't need to put any formula in the other three cells - the FILTER (limited by ARRAY_CONSTRAIN) will put the data there for you.
From there I'd say the easiest way to retrieve the bundle value is with a VLOOKUP:
=iferror(vlookup(E23,Data!A$3:C$2000,3,false),"")
So, look in our data range for the item value in E23, then return the third column. There might be faster ways, but this is clean, easy, and for a relatively small amount of data I wouldn't worry about the speed of it. Again, wrapped with an IFERROR. I just filled down for the other three rows - something I always get caught out by with VLOOKUP is forgetting to put the $s in where appropriate.
Here's the copy I made to work on, with the formulas filled in to see: https://docs.google.com/spreadsheets/d/1yKv6f9ciySvSPJ0Iy2AmfehpO7dVj38HWnlS2slrc5M/edit?usp=sharing
Unfortuately I don't have enough reputation to post links to each of the functions full documentation, but you can find them all here: https://support.google.com/docs/table/25273?hl=en
Hope that helps!
In Google Sheets, when you copy a formula that uses relative references to another cell, that references are automatically updated.
A basic example
If in cell B2 the formula is =A2 and you copy it and paste to cell B43, the resulting formula will be =A43.
An example for the specific case of this question
If in cell B2 the formula is
=HYPERLINK(CONCATENATE("http://example.com?id=",A2),A2)
and you copy it and paste to cell B455, the resulting formula will be
=HYPERLINK(CONCATENATE("http://example.com?id=",A455),A455).
References
Add formulas to a spreadsheet - Docs editors help
Best Answer
I suspect that a third-party application or browser plug-in is interfering with the Windows clipboard and causing your problem.
Whatever the issue is, you may be able to quickly accomplish your copy/paste goal by using the Web Clipboard tool inside of Google Docs. (Look for the collapsible section called "The web clipboard tool".) The directions are not well written, though. Basically, select A2:A7, click Edit/Web clipboard/Copy. Select your destination cell. Click Edit/Paste (or Paste Special if you prefer. In fact, a few years ago I had a copy/paste issue that I could only resolve by using Paste Special but I cannot remember the details).
Because the Web clipboard goes through the Google server, if it does not work, then submit a bug report.
I believe it will work, though. If it does work, you can troubleshoot the cause of the problem by attempting the same action on another computer, on the same computer but in a different browser, on the same computer but with all background programs disabled (especially anything that looks at your clipboard such as anti-virus, screen capture, MS Office, or a clipboard manager), same browser but with plugins disabled, and/or copy the cells and then paste into notepad (it should put the data on six lines. I thought of at least one more troubleshooting idea but it's 2 am and I forgot it while I was writing the list. Oops. Try opening and editing the spreadsheet in LibreOffice or some other spreadsheet software.
I'm also curious what would happen if instead of selecting only B2, you selected B2:B7 and then pasted.
A potential problem is if you have multiple languages and keyboard inputs installed on your computer. When Windows switches from left-to-right languages to RTL languages, whitespace characters often do crazy things. So, you probably want to avoid selecting Arabic or other RTL languages while editing. If you have MS Maren or the language bar enabled, they might mess with you, too.
In the spreadsheet, is there anything in Script Manager?
I'm either out of ideas or too tired to think of more things. Good luck!