VLOOKUP/INDEX MATCH are great but they fail when you have duplicates in the lookup column. Using TEXTJOIN as an alternative can change this (together with an IF clause wrapped inside an ARRAYFORMULA function). If & is considered like + but for text, TEXTJOIN is considered like the SUM function, and the text version of SUMIF is what we need, but you need the combination shown to achieve it. Note the same method works in Excel with office 2021 or more recent but the arrayformula wrapper isn't needed.

Example files can be found here: xlconsulting-asia.com/youtube-files

Here is the formula if a delimiter of 😲 is used between entries: =ARRAYFORMULA(TEXTJOIN("😲",TRUE,IF(H10=$F$4:$F$10,$A$4:$A$10,"")))
In Excel you only need =TEXTJOIN("😲",TRUE,IF(H10=$F$4:$F$10,$A$4:$A$10,""))

I also describe how to deal with looking up in multiple columns, where you need to match multiple columns, this would be the equivalent of TEXTJOINIFS. I later show how the SPLIT function can be used in addition to get each result in its own column. If you want it still in one column you can use FLATTEN with the process I describe here: youtu.be/lCB6aXcQdV4, note these later methods wouldn't work in Excel

00:00 - Introduction
00:45 - TEXTJOIN intro
02:27 - TEXTJOINIF
03:52 - Lookup with duplicates
05:10 - SPLIT function
05:49 - Return matches on multiple columns
07:21 - Lookup with multiple columns

• Lookup with duplicate values in Google Sheets with TEXTJOIN ( Download)
• TEXTJOIN Function, Like VLOOKUP with Multiple Matches - Google Sheets Tutorial, IF, TEXTJOIN, Arrays ( Download)
• Lookup and Return Multiple Results into One Cell Separated by Comma or Similar Delimiter ( Download)
• XLOOKUP Only Returns First Value | XLOOKUP Return all Matches Vertically Or All Matches in One Cell ( Download)
• Find duplicate matches with this TEXTJOIN trick!..... ( Download)
• Google Sheets: TEXTJOIN ( Download)
• Google Sheets TEXTJOIN Function | Join Multiple Values with a Delimiter | Google Sheets Functions ( Download)
• Lookup & Return Multiple Values in One Cell in Excel (Easy Formula) ( Download)
• Conditionally Join Texts With TEXTJOIN and IF functions in Excel ( Download)
• Find duplicate matches with this TEXTJOIN trick!... ( Download)
• VLOOKUP All Matches with this Crazy Simple Trick ( Download)
• Merge Duplicate Rows in Excel Combining Unique Values in One Cell ( Download)
• Multiple Value Lookup in Single Cell (By using UNIQUE & TEXTJOIN FORMULA) ( Download)
• Google Sheets Duplicates Check & Extract: Powerful COUNTIFS, FILTER & TEXTJOIN Functions! ( Download)
• Return Multiple Match Results in Excel (2 methods) ( Download)