VLOOKUP with two lookup values

Mindlesh

Board Regular
Joined
Apr 2, 2014
Messages
172
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
YearRate
20172017NI5%
TX2016RI2%
NI5%
RI10%
2016
TX
NI7%
RI2%
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)
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top