I want to use LOOKUP but the entries in my rows/columns are not in ascending order. Is there an alternative function that will do this.
I have an entry in J3 and I want to find out which column that value first appears in the array L3:JB3
I want to use LOOKUP but the entries in my rows/columns are not in ascending order. Is there an alternative function that will do this.
I have an entry in J3 and I want to find out which column that value first appears in the array L3:JB3
Hi Albatross,
You should be able to use the Match function, with the third argument set to 0 or False for an exact match. That way, the list - as when using LOOKUP or VLOOKUP - doesn't need to be sorted. For example:
=MATCH(J3,L3:JB3,0)
That will return the position in L3:JB3 where J3 was found. So if it was found in cell P3, the value 5 would be returned (P being the 5th column in the range.. L, M, N, O, P...)
A little additional math and you can get the address of the match instead, like adding 11 to the MATCH function to return 16. The sixteenth column in a worksheet is P (16th letter).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks