Jul 26 2022 11:16 PM
when I use vlookup , and I want to fill in the column index, I no longer see numbers in the columns when I select the table array. It is difficult for me to count them, because sometimes it is column 58. Do you know a solution here?
Jul 27 2022 12:00 AM
Hi @sanneb1985
yes, if you use large tables, sometimes the column numbers disappear. As a workaround, you could add a row above your table and use the COLUMN function there. This gives the absolute column number:
Please note, if your table does not start in column A, you need to add the number of additional columns to the formula. For example, if it starts in column D, you need to subtract 3 to get the correct number for your index:
Jul 27 2022 12:05 AM
Do you have Microsoft 365 or Office 2021? If so, you can use XLOOKUP instead of VLOOKUP:
=XLOOKUP(lookup_value, column_to_search, column_to_return, if_not_found)
For example
=XLOOKUP(A2, $C$2:$C$100, $BD$2:$BD$100, "")
If you have an older version, you can use INDEX/MATCH:
=IFERROR(INDEX($BD$2:$BD$100, MATCH(A2, $C$2:$C$100, 0)), "")
Jul 27 2022 12:32 AM
Jul 27 2022 12:35 AM
Jul 27 2022 12:35 AM
@sanneb1985 Vlookup may create performance issue when there is selection of more columns in huge database, so better use INDEX MATCH formula.
Excel Vlookup Index Match - Excel Tips - MrExcel Publishing
Jul 27 2022 12:48 AM
Jul 27 2022 12:49 AM