VLOOKUP WITH HLOOKUP

[Jewells0905]

I am trying to write a formula using VLOOKUP with HLOOKUP as the index. This formula will return the annual revenue for a given salesperson in cell B12 and year in cell B13.

I am currently using formula =VLOOKUP(HLOOKUP(B13,B2:F3,2,FALSE)&B12,A4:F10,1,false) but I am getting #N/A error, stating - Did not find value '4Jimmy' in VLOOKUP evaluation.

	2018	2019	2020	2021	2022
Column #	2	3	4	5	6
Jimmy	$30,150	$33,497	$34,608	$34,214	$32,768
Freya	$37,386	$34,512	$36,573	$37,949	$34,980
Artie	$40,836	$45,833	$43,827	$42,997	$42,195
Marie	$27,212	$27,137	$29,241	$29,975	$26,916
Dwayne	$16,133	$16,950	$15,412	$17,356	$16,862
Grace	$41,914	$44,695	$43,975	$38,484	$43,772
Westin	$19,259	$17,403	$23,897	$18,146	$27,546
Salesperson:	Jimmy
Year:	2020
Revenue:

 

[Cubist]

Try:

Book3
	A	B	C	D	E	F
1
2		2018	2019	2020	2021	2022
3	Column #	2	3	4	5	6
4	Jimmy	$ 30,150	$ 33,497	$ 34,608	$ 34,214	$ 32,768
5	Freya	$ 37,386	$ 34,512	$ 36,573	$ 37,949	$ 34,980
6	Artie	$ 40,836	$ 45,833	$ 43,827	$ 42,997	$ 42,195
7	Marie	$ 27,212	$ 27,137	$ 29,241	$ 29,975	$ 26,916
8	Dwayne	$ 16,133	$ 16,950	$ 15,412	$ 17,356	$ 16,862
9	Grace	$ 41,914	$ 44,695	$ 43,975	$ 38,484	$ 43,772
10	Westin	$ 19,259	$ 17,403	$ 23,897	$ 18,146	$ 27,546
11
12	Salesperson:	Freya
13	Year:	2018
14	Revenue:	$ 37,386
Sheet3

Cell Formulas
Range	Formula
B14	B14	=VLOOKUP(B12,$A$4:$F$10,MATCH(B13,$B$2:$F$2,0)+1,FALSE)

 

[Jewells0905]

Try:

Book3
	A	B	C	D	E	F
1
2		2018	2019	2020	2021	2022
3	Column #	2	3	4	5	6
4	Jimmy	$ 30,150	$ 33,497	$ 34,608	$ 34,214	$ 32,768
5	Freya	$ 37,386	$ 34,512	$ 36,573	$ 37,949	$ 34,980
6	Artie	$ 40,836	$ 45,833	$ 43,827	$ 42,997	$ 42,195
7	Marie	$ 27,212	$ 27,137	$ 29,241	$ 29,975	$ 26,916
8	Dwayne	$ 16,133	$ 16,950	$ 15,412	$ 17,356	$ 16,862
9	Grace	$ 41,914	$ 44,695	$ 43,975	$ 38,484	$ 43,772
10	Westin	$ 19,259	$ 17,403	$ 23,897	$ 18,146	$ 27,546
11
12	Salesperson:	Freya
13	Year:	2018
14	Revenue:	$ 37,386
Sheet3

Cell Formulas
Range	Formula
B14	B14	=VLOOKUP(B12,$A$4:$F$10,MATCH(B13,$B$2:$F$2,0)+1,FALSE)

that did work thank you so much for your help. I also figured out that the below formula works and uses both VLOOKUP and the HLOOKUP functions
=VLOOKUP(B12, A4:F10, HLOOKUP(B13, B2:F3, 2, TRUE), FALSE)