Excel 365 - Vlookup issue

Copper Contributor

@safikul6190 _ While using Excel 365, wherein during V lookup function, the col_index_num / table array details are not displayed as it used to be while using the previous version of Excel

6 Replies
can anyone help with this issue

@safikul6190 

Could you please be more concrete, what exactly is wrong? Small sample file could help.

@Sergei Baklan, when you apply the function vlookup=(lookup_value,table_array,col_index_num,[range_lookup]), post selection the table_array or the data you are able to see the col_index_num field next to the highlighted area in the previous version which you enter so that you can get the value from the column you are looking for. However, in Excel 365 the selection field is not highlighted and you have to manually count the column or have to enter number value at the top of the table to recognize the column you need the value from. This seems to an issue with Excel 365 and not with any file.

You could use the Columns function instead of hard coding the column number. That might make it easier to see what column is being returned without having to count columns. Also, the formula would be resistant to columns being inserted into the table and shifting the column you want to return to the right (so, instead of column 2 it becomes column 3).
=VLOOKUP(E1,A1:D10,COLUMNS(A1:C10),0)

Or, you could use Index/Match (or the new xlookup if you have it) instead of vlookup, which would make it easier to tell what column is being returned.
=INDEX(C1:C10,MATCH(E1,A1:A10,0))

@safikul6190 

If you are an Excel 365 user, why not switch to XLOOKUP?  It is better than VLOOKUP in almost all respects.

@safikul6190 

In support to previous posts, with XLOOKUP it's as easy as

=XLOOKUP(date, Table[Date], XLOOKUP(shopName, Table[#Headers],Table), "no such")

if shopName is one of columns name from which you'd like to return the value.