Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Some kind of lookup table? (2000)

    I

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Some kind of lookup table? (2000)

    Easiset way is to add a column C to your lookup table between the location and the rate. This should COMBINE the type and location using a formula like:
    =A2&B2
    and copied down the range (I started in 2 since I figured row 1 has headers). This can be paste-special - valued to remove the formulas later.

    Then your lookup is:
    =VLOOKUP(A1&B1,Lookup!$C$1:$D$100,2,FALSE)

    I assume that your "lookup table" is in a sheet called "Lookup" and the range goes to 100 (change as appropriate).

    Copy this down the rows

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Some kind of lookup table? (2000)

    To get the tax rate, what you are trying to do is something like
    rate = index(rates,type,location)
    So, let's say you assign a range name 'typeCol' to the tax Type column on your rate sheet.
    Then assign a range name, say 'locationRow', to the row containing the Locations on the rate sheet
    Assign a name to the rate table block, say 'rateBlock'

    Then, if your expense type was in say, cell [a2], and your location was in cell [b2]..
    ..then to return the lookup tax rate in say cell [c2], use the formula
    =index(rateBlock,match(a2,typeCol,false),match(b2, locationRow,false))

    ..adjust accordingly

    zeddy

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Some kind of lookup table? (2000)

    This is better than mine. I hadn't thought of rearranging the table from col of type, col of location, col of rate to a 2 way table of col of type, row of location and the intersection of the values.

    No need to concatenate.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some kind of lookup table? (2000)

    Whoa ... I'd ha' never been able to figure this one out alone! Now that I'm a bit clued in, I can use this for some other spreadsheets where I kludged together some horrendous IF statements. Thanks!

  6. #6
    New Lounger
    Join Date
    May 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some kind of lookup table? (2000)

    Zeddy, I am trying to understand this INDEX formula and am not able to get get this to work. Can you please take a look at this attachment, it is just a trial table that I made. When I enter the column number in column A and the row location in column B, I am looking for the intersection of values in column c. Thanks, Ken

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Some kind of lookup table? (2000)

    The columns and rows are backwards: Index (Data, row, col), you are getting Index(data,col,row)

    <pre>=INDEX(rateBlock,MATCH(B3,locationRow,FALSE), MATCH(A3,typeCol,FALSE))</pre>


    Steve

Posting Permissions

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