+ Reply to Thread
Results 1 to 2 of 2

Alternative to LOOKUP function

  1. #1
    Registered User
    Join Date
    04-23-2010
    Location
    Ashford, England
    MS-Off Ver
    Excel 2007
    Posts
    27

    Alternative to LOOKUP function

    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

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Alternative to LOOKUP function

    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).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1