# Thread: Some kind of lookup table? (2000)

I

2. ## 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.

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

zeddy

4. ## 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. ## 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. ## 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. ## 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
•