[Solved] VBA: UDFs for Lookup Function

    Recently I was creating a spreadsheet where I needed to use heaps of nested vlookups within a hlookups - I tried without success to create a UDF. -


    My spreadsheet formula was


    =HLOOKUP(E11,$M$36:$Q$49,VLOOKUP(F11,$M$36:$Q$49,2,FALSE),FALSE)


    The UDF variables were hlu = E11, vlu = F11 & agmass = $M$36:$Q$49.


    The UDF started 'Function LU(vlu, hlu, agmass)

    But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

    Will this work for you?


    Function myLookup(HLU As Range, VLU As Range, AGMASS As Range)


    myLookup = Application.HLookup(HLU, AGMASS, Application.VLookup(VLU, AGMASS, 2, False), False)
    End Function


    Chris

    Chris, (Or should I say, Alfred E Newman?) - excellent, many thanks.


    The one conundrum is that the array needs to have an additional column to identify what the column number is - does that make sense?


    Is there a way the Vlookup can count the array (row) number?


    Again thanks - the result is brilliant!


    Graham

    But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

    Hope no-one minds me butting in :wink2:


    Graham, can you explain what you mean by your last statement - i'm not quite sure I follow what you mean.... is the VLOOKUP supposed to be returning a column value for the HLOOKUP (I guess so)....


    An example worksheet would be an excellent way to help solve the problem I think.


    Just as an aside..... If you are going to use this UDF alot in your worksheet, you will find that although the longer (original) version looks messy, it will be a good deal faster than your UDF when it comes to recalculating the worksheet.... this is something I have learned from experience...


    Just my 2p's worth.


    Will

    Will!, Pesky!


    Nay to 2ps, 5ps - no way - the suggestion is worth a complete shilling! Make it 2-bob, if you can tell me what function counts a column position


    (as an example, a column of 10 words starting at A5. I use vlookup and find my word in A10, what function tells me it is in the 6th row of the column?)


    I reckon its hard but I am only a poor (read less than a shilling) sod!


    ps Pesky, what is two bob worth anyway???

    But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

    Bob = shilling = 5p = 10c (where I am)


    2 Bob = Florin = 10p = 20c


    ps:


    Match is the function.


    Data is in A5:A15
    =MATCH("myVal",A5:A15) will tell which occurance matches myVal. This works on either a horizontal (A1:H1) or vertical (A1:A15) selection but doesnt work over a two dimensional array.


    W.

    G,
    If you want to get the absolute row as opposed to the row of the lookup array...


    i.e.
    You have the word 'lazy' in A1
    this is repeated in A6 (in your A5:A15 array)


    You want the row 6 as opposed to the 2nd row of the array ??


    In that case


    =4+MATCH($A$1,$A$5:$A$15,0)


    will return 6


    Hope this helps


    Will

    You fella's are amazing!, how do you know this stuff?


    Forget the shillings, florines - go straight to sterling (One pound, one shilling - if my aging memory is correct)


    I will put on my thinking cap and try and pose a very difficult problem - however, right now AFL bloopers is on the box -a must see!


    Will, AFL is our local brand of violence we call footy. We have a bloke over here called Roca who has been rubbed out for 2 weeks because he half hit an opposing player with his elbow, With him gone hopefully the other team (read) Lions will cream the Maggies - (only a personal hope nothing political) - I am sure Pesky will have a view!

    But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login