VLOOKUP with two lookup values

[Mindlesh]

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?

=VLOOKUP((IF(MONTH(NOW())<4,YEAR(NOW()),YEAR(NOW())+1)), Table, MATCH("Rate"), Table[#Headers], 0), FALSE)

 

[Mindlesh]

I can use INDEX to get a Rate; just not the one for the right Year.

=INDEX(Table,MATCH("Rate", Table[#Headers], 0),15)

 

[KenDue]

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]

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]

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.

 

[Aladin Akyurek]

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]

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)

 

[Mindlesh]

=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!

 

[Mindlesh]

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

That also works; thank you!

 

[Tetra201]

You are welcome.