Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup a value in a table

    I have a function in vba that returns a value which is based on some value I have to look up in a table. This table has the first row with unique identifiers, defining the column index where I have to find my value. But the first two columns (and not the first column alone) define the unique identifier for the row. Once row and column are found, the value is found at the cross section.
    I tried to work it out with a combination of HLOOKUP and VLOOKUP, but couldn't find a solution. Finally, I wrote a macro that does the job, but it tremendously slows down my spreadsheet application.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    Could you insert a column which combines (&) the contents of the first 2 columns, and make that lookup column. It could be hidden from view if required. Then you could combine VLOOKUP with MATCH (or one of the other Lookup functions) to find what you want. I'm probably missing something, but that seems to be the simplest method.

    Andrew C

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    You are right, Andrew. Thanks, I haven't thought of that, but I can easily combine the first two columns into one, resulting in an unique identifier for the row. It's just because I received the table like this and I didn't even consider to change the table layout, but, why not, it can easily be done.
    Then I still need to pick up a value from a table, where both the columnindex and rowindex are important. Is that possible combining worksheetfunctions? All I know is the value (actually it is text) in the first row and the value (also text) in the first column. From these values, I need to obtain the row and columnindex, and then I want to pick up the contents of the cell at the intersection.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    Hans, you should be able to combine VLOOKUP with MATCH to get the value you need. Use MATCH to get the column index using =MATCH("text",Range,0) - where "text" is the value of a column header and the final zero means find exact match. Use VLOOKUP to find the row using the result of The MATCH() to pint the column.

    =VLOOKUP(XZ1,A1:M100,MATCH(Y1,A1:M1,0),FALSE), where A1:M100 is the data table, and Z1 holds a value in Column A and Y1 holds a value in Row 1, i.e. Z1 and Y1 are the values you are looking for. Hope you can follow that and that it works for you. Depending on the exact layout, you may have to add an offset to the result of the MATCH() function.

    Andrew

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    <hr>I wrote a macro that does the job, but it tremendously slows down my spreadsheet application<hr>
    That surprises me- but I'm basing that on my own experience and criteria.

    I had an application with all sorts of complex lookups, with very large tables to lookup. I was getting 3 minute responses for ever change of value- obviously not desirable.

    I changed the lookup formulae into VBA code- and I achieved a response less than 5 seconds.

    My formulae were complex, and there were lots of them. There obviously came a time when the spreadsheet became overloaded.

    I also found that, in that circumstance, there was a sort of threshold where thre performance differed tremendously. I had something like 5 columns, and was entering values in by "cells(x,y) = value". I added one more column- and the worksheet calculation gound to a halt. In this circumstance, I found that performing calculations in an array, and assigning the array to a named portion of the spreadsheet, gave huge improvements in performance.

    I'm not saying VBA is better than cell formulae. I'm just suggesting that it depends on the circumstance. If the VBA code is hugely better to maintain than a cell formula, it may be worth while to start looking at ways to improve the VBA. You may then also have the benefit of being able to do heaps more with the code you've produced- at least, taht's what I've found.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    Geoff,

    I don't know if the worksheetfunction will be faster. I didn't know how to do it with worksheetfunctions, before Andrew helped me with it. The problem is not the speed in case I only need this function once, speed becomes a problem if recalculation is set automatically and if hundreds of these functions are present in my spreadsheet. I just thought, that using worksheetfunctions might be faster. In fact, in John Walkenbach's book 'Excel 2000 Power Programming with VBA' he compares in two different examples the performance of
    1) the built-in worksheetfunction UPPER and a VBA function that he wrote to do the same, which he called UpCase. He created a worksheet that called the function 10000 times, using an argument that was 26 characters long. With his function, recalculation took about 13 seconds, while the recalculation time was virtually instanteneous with Excel's built-in function
    2) the built-in function SUM, and the equivalent self-written vba function, which he called MySum. A worksheet with 1000 functions recalculated instantaneous with the SUM function, but it took about 12 seconds to do the same with the self-written function

    There comes my wisdom from, Geoff. I haven't tried it out myself, but as I wasn't happy with the speed of my function, I thought it could be done faster, using Excel's built-in function. I'll try out what Andrew proposed, next Tuesday, when I am back at the office, and compare it with my vba solution. I'll let you know the result.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    Thanks a lot Andrew, that is what I was looking for. I hadn't made use of the MATCH worksheetfunction before, so I didn't think of combining this with VLOOKUP or HLOOKUP. Now I hope that the built-in worksheetfunction is faster than the VBA function I wrote. Geoff has some doubts about that, as you might have seen from his post. I'll try to make the comparison, Tuesday when I am back at the office. I'll let you know about the result.

    Thanks again.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    Hans,

    In my case, I had a worksheet full of complex lookups- and every time I had a change, it became a real pain. I was able to replace complex lookups with simple code- that was the difference. I was able to do things in code like "If cellA = something and cellB like something but cellC not like something" very simply, which I could not do simply with formulae- and that's what made the difference in performance. Each of the lookups in hundreds of cells had to perform a lookup in the same very big volume of data- that's what caused the delys in my app. The code only had to read through that data once. The improvement in speed comes from the simplification of the processing.

    And, come to think of it, I didn't just replace the forumale with a reference to the VBA procedure. I completely changed the way it calculated. I "pushed" values onto the worksheet. Whenever data in the source range changed, the VBA sprang into action, calculated values, and copied them into named ranges in the target worksheet.

    But I'm still surprised how slow your function was!
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    Hans, if you are copying the formula down a number of rows, the MATCH() function should refer to the header row, and so needs absolute references. =VLOOKUP(Z1,A1:M100,MATCH(Y1,A$1:M$1,0),FALSE)

    Hope it does the job for you

    Andrew

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup a value in a table

    Andrew,

    Thanks. I checked it out and it's really what I need. I know about absolute references. Anyhow, I still need to do some VBA work and I want to use your solution in my VBA code, using Application.WorkSheetFunction, instead of the For .. Next loops that I use now to search through the table to find the column and row indexes.

Posting Permissions

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