How to Return a Website URL by Company Name Using INDEX MATCH or XLOOKUP
Question details
The user needs to find a company name on one worksheet and return its associated website URL from a different worksheet, preferably as a clickable link.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Cross-referencing company data across multiple sheets where the lookup column (company name) and the return column (website URL) are not positioned ideally for a standard VLOOKUP.
- Observed behavior
- Requires a formula combination that correctly matches the company name in another sheet and outputs the corresponding website URL from the same row without throwing errors for missing data.
Ensure that the company names in both worksheets are spelled exactly the same without any hidden leading or trailing spaces, as mismatching text will result in formula errors.
Use INDEX and MATCH with IFERROR
This is the most reliable method for looking up data, especially when the return column is to the left of the lookup column, which VLOOKUP cannot handle natively.
The INDEX and MATCH combination is a powerful alternative to VLOOKUP. By utilizing MATCH to find the row number of the company name and INDEX to retrieve the website from that specific row, you can overcome VLOOKUP's left-to-right limitation.
Click on the cell in Sheet1 where you want the website URL to be displayed (for example, next to the company name in cell F2).
Assuming your company names are in Sheet2 column E and websites in Sheet2 column C, type the following formula: =IFERROR(INDEX(Sheet2!$C:$C,MATCH(F2,Sheet2!$E:$E,0)),"")
To ensure the retrieved website is an active, clickable link, wrap the entire successful lookup in a HYPERLINK function: =IFERROR(HYPERLINK(INDEX(Sheet2!$C:$C,MATCH(F2,Sheet2!$E:$E,0))),"")
Press Enter to execute the formula. Then, click the small square at the bottom-right corner of the cell and drag it down to apply the lookup to the rest of the companies in your list.
Use the XLOOKUP Function
A simpler, more modern formula available in newer spreadsheet software that replaces the need for complex INDEX MATCH nesting.
Use WPS Spreadsheet to Quickly Cross-Reference Data
WPS Spreadsheet fully supports advanced lookup functions including INDEX, MATCH, XLOOKUP, and HYPERLINK. You can easily manage complex datasets and build dynamic, cross-sheet data connections with familiar Excel formulas.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the company data across different sheets.
- 2. Navigate to the target cell: Click on the cell where you want the matching website URL to appear.
- 3. Insert the lookup formula: Type your preferred lookup formula, such as =XLOOKUP(F2,Sheet2!$E:$E,Sheet2!$C:$C,"").
- 4. Apply and drag: Press Enter to see the result, and use the fill handle to drag the formula down to automatically cross-reference the rest of your data.
Frequently Asked Questions
Why is my INDEX MATCH formula returning an #N/A error?
The #N/A error indicates that the lookup value (the company name) does not perfectly match any entry in your lookup array. This is often caused by accidental leading or trailing spaces. You can use the TRIM() function on both datasets to clean up spaces before matching.
Why should I use INDEX MATCH instead of VLOOKUP?
VLOOKUP requires the value you are searching for to be in the leftmost column of your data table array. If your company names are in Column E and the websites are in Column C, VLOOKUP cannot look 'left' to find the URL. INDEX MATCH does not have this limitation.
Can I return a hyperlink with a friendly display name instead of the full URL?
Yes. The HYPERLINK function takes two arguments: the URL and an optional friendly name. You can format it like this: =IFERROR(HYPERLINK(INDEX(Sheet2!$C:$C,MATCH(F2,Sheet2!$E:$E,0)), "Visit Site"), "")
