lookup_value as array, why lookup over vlookup, multiple lookup_value in single lookup function, sum(lookup)
In one of my earlier posts, I had illustrated passing an array for col_index_num in vlookup. Refer Extract and sum vlookup results.
We now look at the possibility of passing an array for lookup_value in vlookup. Refer the example below:
If we were required to sum the sale of Prod3 for Angie, Mike and Ross (cells highlighted in yellow), can we do it through a single vlookup function. Lets say an array is defined as {Ross, Mike, Angie} as salesperson.
This vlookup function
{=SUM(VLOOKUP(salesperson,A3:D7,4,FALSE))} entered as an array with ctrl+shift+enter gives the result as 3 – which corresponds to sale of Prod3 by Ross (first element in the array salesperson). So clearly the array function is not working!
Lets now try the lookup function. Revise the above formula as
{=SUM(LOOKUP(salesperson,A3:D7))}
OR
{=SUM(LOOKUP(salesperson,A3:A7,D3:D7))}
which depends on your comfort with the two syntax for lookup
- LOOKUP(lookup_value,array)
- LOOKUP(lookup_value,lookup_vector,result_vector)
entered as an array formula with ctrl+shift+enter
The formula works and it correctly returns 114
The point to notice here is that lookup_array needs to be in ascending order.
Have fun. This is only the second time that I have used lookup function. It also explains why in the previous post – Text to numbers – method 2, I have used lookup and not vlookup.
Have fun 🙂
Leave a comment