vlookup column index number

[sanneb1985]

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?

[#2]

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:

[#3]

@sanneb1985 

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)), "")

[#4]

We still have office Professional plus 2019 at work. We used to have 2016. Is it possible that Microsoft has made changes in the new version? My collegue still has the 2019 and the column numbers are visible (we use the same files for vlookup)

[#5]

@sanneb1985 

Perhaps your colleague uses R1C1 reference style?

[#6]

@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

[#7]

no , it is not that. She also has letters in the columns, only when she select the range, numbers appear above each column.

[#8]

i'll dive into that and will read the tips, thank you