Vlookup/IndexMatch if value is found in a range

[bnbcat]

Hello!

I just returned to work from maternity leave and my brain is a little fuzzy. This seems like a pretty simple lookup, and I'm 99% overthinking this, but I can't figure it out, and Googling hasn't been helpful. Any help is appreciated!

The table looks like the below, and I need the formula to find the specified time period (columns A-C) and pull the period (column D).

	A	B	C	D
1	YEAR	H1	H2	Period
2	2020	2020 H1	2020 H2	Per 1
3	2021	2021 H1	2021 H2	Per 2
4	2022	2022 H1	2022 H2	Per 3

IE: 2021 H1

I need a formula that would searches the above table, finds that 2021 H1 exists in row 3 and will therefore pull Period 2.

 

[MrTomasz]

... edit ...

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	YEAR	H1	H2	Period
2	2020	2020 H1	2020 H2	Per 1				2021 H1	Per 2
3	2021	2021 H1	2021 H2	Per 2
4	2022	2022 H1	2022 H2	Per 3
5
Sheet2

Cell Formulas
Range	Formula
I2	I2	=INDEX(D2:D4,MATCH(H2,INDEX(B2:C4,,MATCH(RIGHT(H2,2),B1:C1,0)),0))

 

[bnbcat]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	YEAR	H1	H2	Period
2	2020	2020 H1	2020 H2	Per 1				2021 H1	Per 2
3	2021	2021 H1	2021 H2	Per 2
4	2022	2022 H1	2022 H2	Per 3
5
Sheet2

Cell Formulas
Range	Formula
I2	I2	=INDEX(D2:D4,MATCH(H2,INDEX(B2:C4,,MATCH(RIGHT(H2,2),B1:C1,0)),0))

Worked perfectly! I've also updated my info. Thank you so much!

 

[Fluff]

You're welcome, thanks for the feedback & updating your profile.