Extend VLOOKUP() and HLOOKUP()
Extend the lookup functions so they can lookup left/up as well as right/down. That will save us having to build and maintain unintuitive formulae combining INDEX() and MATCH().
Malcolm G
shared this idea
Thanks for taking the time to post a suggestion Malcolm! We’ll be taking a look at lookup functions. There’s a number of votes on the site for them – I’m not going to merge all of them though because different posts are asking for slightly different things. Please keep voting for the ones you want to see most!
Cheers,
John [MS XL]
16 comments
-
Paul Demaerel
commented
I agree that it is not necessary to have both formulas, I proposed both a simple and a more complicate syntax just to be in line with SUMIF / SUMIFS and others. I still use SUMIF for a simple query and SUMIFS when I need multiple arguments. A matter of taste.
-
Kenneth Barber commented
There's no point in GETIF if GETIFS can do everything that GETIF can do, plus more.
The only reason why we have both SUMIF and SUMIFS is because SUMIF came first and then SUMIFS was introduced later. The only perk to SUMIF over SUMIFS is that you can omit SUMIF's last argument and treat the criteria range as the sum range as well. However, your GETIF has no such perk. Just rename your GETIFS function to GETIF and do away with your original GETIF.
Also, a problem with SUMIFS, which is also reflected in GETIFS, is that OR between conditions is unsupported.
-
Paul Demaerel
commented
Hello,
I see there are several posts on improving the lookup functions.
Here's my five cents: introduce two new functions (one for single condition, and a second for multi-condition, similar to =SUMIF() and =SUMIFS()). These functions could replace, simplify and improve a whole range existing functions and home brewn VBA fuctions, and make the results more predictible:= GETIF ( lookup_value , lookup_array , return_array , lookup_type )
= GETIFS ( return_array , lookup_array-1 , lookup_value_1 , lookup_type_1 , … , … , ... )WHERE:
- lookup_value is the value you want to search for- lookup_array is the range (vertical or horizontal) in which to search
- return_array:
- is either the array where the result must be extracted from
- or is one of the following values to return a number:
-2 -> rank in the range (largest to smallest value - sorted) -> this replaces =SMALL()
-1 -> position in the range (last to first - unsorted)
0 -> number of occurrences -> this replaces =COUNT() and =COUNTA()
1 or omitted -> position in the range (first to last - unsorted) -> this replaces =MATCH() and =FIND()
2 -> rank (smallest to largest value - sorted) -> this replaces =LARGE()
- lookup_type: a parameter to specify how near-matches must be treated
- Case insensitive
-4 -> first in range less than
-3 -> first in range less than or equal
-2 -> last in range less than
-1 -> last in range less than or equal
0 or omitted -> exact match
1 -> first in range greater than or equal
2 -> first in range greater than
3 -> last in range greater than or equal
4 -> last in range greater than
- Case sensitive
96 -> first in range less than
97 -> first in range less than or equal
98 -> nearest less than
99 -> nearest less than or equal
100 -> exact match
101 -> nearest greater than or equal
102 -> nearest greater than
103 -> last in range greater than or equal
104 -> last in range greater than -
Lepista
commented
Can we get the vlookup function so that you can use negative cell positions (when you are looking up a column and requiring the data from an earlier column)
-
David Brett
commented
I would vote this down if User Voice so permitted. VLOOKUPs are already over-used (abused) as they encourage huge look-ups over a data grid rather than column specific references.
-
Col Delane
commented
You can make a VLOOKUP look left (and act much like an INDEX/MATCH combo by only referencing two single column ranges rather than all in between) by using CHOOSE with an array (but without making it an array formula), as in:
=VLOOKUP( lookup_value, CHOOSE({1,2}, lookup_column, return_value_range), 2, [range_lookup] )
-
Ed Hansberry
commented
Allow negative offsets in the VLOOKUP (and HLOOKUP I suppose) function so we can find info based on data not in the first column of a table, and we don't have to mess with a tedious combination of INDEX and MATCH functions.
-
Andre Terra commented
Rather than =VLOOKUP("foo",A1:E5,MATCH("bar",A1:E1,0),0) it would be much more intuitive to have =XLOOKUP("foo",A1:E5,"bar",0) and have it return the first matching header from the left to the right.
-
Corey Becker
commented
I have added my proposals for this here: http://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10801263-re-engineer-vlookup-to-add-the-flexibility-of-inde
I think they can keep it simple by using consistent layout that other functions use and still gain performance and flexibility of INDEX/MATCH
-
Stephen Eddleston-McGrath
commented
A consolidation of the INDEX and MATCH functions to provide a "crosshair" lookup would be handy
CROSSHAIR(ColumnArray,ColumnMatch,ColumnMatchType,RowArray,RowMatch,RowMatchType)
Where ColumnArray is the vertical area you want to search, and ColumnMatch is what you are looking for. ColumnMatchType is the match type (will usually be exact match).
RowArray, RowMatch and RowMatchType are the equivalents for the horizontal area. The result will be the contents of the cell at the intersection.This saves referencing the entire area bounded by the ColumnArray and RowArray in the INDEX function, and would also avoid any mistakes caused by the Column and Row areas not intersecting at their top left point.
-
Corey Becker
commented
I'm not sure how Patrick was testing but in the tests I have seen, index/match is often better and any power user will tell you this. I have not met a true power user of Excel that still uses VLOOKUP. It certainly needs an update.
-
Patrick Gelsema
commented
In testing between Vlookup on a table and Index/Match on the same table the Vlookup was much faster. Although in both cases I was referencing the table. I would expect both to have the same performance which it unfortunately doesn't have.
-
Col Delane
commented
There is no need to replace or "fix" VLOOKUP or HLOOKUP - they are not broken! They have a purpose the same as every other function - it's just that many users try to utilise them when another function (or combo) would be more appropriate.
Most users blindly stick with a hard-coded column offset value rather than return it dynamically by using other functions such as MATCH, COLUMN or COLUMNS (either to lookup a varying column position or to cater for subsequent inserting/deleting of columns in the lookup array which changes the required offset value)
An INDEX/MATCH combination is no more difficult/unintuitive than the equivalent VLOOKUP, is more flexible/powerful, and it references far less cells if the VL array spans more than 2 columns.
-
Dory Owen
commented
Maybe some new functions to replace the complex INDEX, MATCH stuff as seen at the link below.
http://trumpexcel.com/2013/09/excel-index-function-its-magical/ -
Anonymous
commented
Index(match()) can do this....and is much better
-
Alexander Carse
commented
Please don't do this. The lookup functions require much more maintenance than index-match and therefore represent a much higher error risk as a result. In fact anything Microsoft can do to encourage people not to use them would be greatly appreciated.