Changes to VLOOKUP/HLOOKUP
I suggest 2 changes in Vlookup/Hlookup:
A. VLOOKUP/HLOOKUP functions user can only search for the lookup value in the first column/row, and the result column/row is always after (right/bleow) the lookup column/row.
I suggest to make it more flexible by letting the user decide which column to search in and which columns to retrieve the value from.
B. Sometimes user need to drag lookup function to several columns and needs the column index to be incremented, for example to be 3 in the first column, the second column to be 4 and so on, I suggest to add new parameter to let the user decide
the final format should be:
VLOOKUP(Lookup Value, Table Array, Lookup Column Index, Result Column Index, Increment Result Column "True/Flase", Range Lookup "True/False")
Edward Paul commented
As the discussion is about VLookup and HLookup function so I want to share the information I have on this.
Most of us think that Vlookup and hlookup function are very similar in use but actually its not. They are quiet different from another. Let’s know how..?
Mark Churchill commented
You can do what you describe already, using INDEX and MATCH
INDEX('Result column array', MATCH('Lookup value', 'Lookup column array', 0))
No need to move any columns.
Mohamed Hemdan commented
I have now the XLookup but is only on Mac Version, not on my windows version ( I work on both)
Anon and Cody. Thanks for the 411. XLOOKUP is currently available to me. Valid values for match-mode and search-mode are still not clear to me.
Have you tried XLOOKUP?
Amr Abdou commented
Mohammed Taha commented
Vote of course to this idea, either same order of command or make the column index optional to the last to keep old order
Omar Elbatal commented
I support this idea