# VLOOKUP with two lookup values

#### Mindlesh

##### Board Regular
I have a table with groups of four rows: Year, TX, NI, RI; only Year changes. How can I lookup both Year and NI to get Rate?
Code:
``=VLOOKUP((IF(MONTH(NOW())<4,YEAR(NOW()),YEAR(NOW())+1)), Table, MATCH("Rate"), Table[#Headers], 0), FALSE)``

Last edited:

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Mindlesh

##### Board Regular
I can use INDEX to get a Rate; just not the one for the right Year.
Code:
``=INDEX(Table,MATCH("Rate", Table[#Headers], 0),15)``

#### KenDue

##### New Member
Not enough information has been presented. What is the layout and content of your lookup table?

Can a concatenation of your "year" and "NI" cells relate to that lookup rate table?

#### Mindlesh

##### Board Regular
This is how the table looks:
 Year ~ Rate 2017 ~ TX ~ NI ~ 5% RI ~ 10% 2016 ~ TX ~

<tbody>
</tbody>
I want to get the Rate at the intersection of NI and Rate, using the formula for the Year.

#### KenDue

##### New Member
I'm afraid I can't be of any help to you on this one... what you are presenting as a lookup table is completely foreign to anything I have ever seen or done in the way of lookups. I hope someone else understands the concept you are describing.

##### MrExcel MVP
 Year Rate 2017 2017 NI 5% TX 2016 RI 2% NI 5% RI 10% 2016 TX NI 7% RI 2% 2015

<tbody>
</tbody>

In G2 enter and copy down:

=VLOOKUP(F2,OFFSET(INDEX(A:A,MATCH(E2,A:A,0)),0,0,4,2),2,0)

#### Tetra201

##### MrExcel MVP
I have a table with groups of four rows: Year, TX, NI, RI; only Year changes...
Assuming your "lookup" year is in cell E2, try the following formula:

=INDEX(C:C,MATCH(E2,A:A,0)+2)

Last edited:

#### Mindlesh

##### Board Regular
=VLOOKUP(F2,OFFSET(INDEX(A:A,MATCH(E2,A:A,0)),0,0,4,2),2,0)
Thank you; that's exactly what I wanted!

Last edited:

You are welcome.

Replies
6
Views
127
Replies
9
Views
194
Replies
5
Views
124
Replies
1
Views
60
Replies
1
Views
206

1,190,800
Messages
5,982,984
Members
439,810
Latest member
phobo3s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back