Vlookup and Hlookup Formula

 

Vlookup Formula

 

=Vlookup(A14,A1:F11,5,0)

	A	B	C	D	E
1	Product ID	Product Name	HSN Code	Seller Name	Qty Sold
2	101	HardDisk	AP0101	Ram Traders	23
3	102	Microphone	AP0102	Rajesh	20
4	103	Mobile Stand	AP0103	Shivam	30
5	104	Laptop Stand	AP0104	Sethi	40
6	105	Mic Holder	AP0105	Rahul	34
7	106	Printer	AP0106	Aman	54
8	107	Scanner	AP0107	Sohan	30
9	108	Monitor	AP0108	Chandu	10
10	109	Mouse	AP0109	Rohan	20
11	110	Keyboard	AP0110	Sonu	25
12
13	Product ID	Qty Sold
14	102	20

 

 

Vlookup Formula Apply in Multiple Columns

=Vlookup($A$14,$A$1:$E$11,Columns($A$13:B13),0) 

or

=Vlookup(A14,A1:E11,{2,3,4,5},False)    Press Ctrl+Shift+Enter

	A	B	C	D	E
1	Product ID	Product Name	HSN Code	Seller Name	Qty Sold
2	101	HardDisk	AP0101	Ram Traders	23
3	102	Microphone	AP0102	Rajesh	20
4	103	Mobile Stand	AP0103	Shivam	30
5	104	Laptop Stand	AP0104	Sethi	40
6	105	Mic Holder	AP0105	Rahul	34
7	106	Printer	AP0106	Aman	54
8	107	Scanner	AP0107	Sohan	30
9	108	Monitor	AP0108	Chandu	10
10	109	Mouse	AP0109	Rohan	20
11	110	Keyboard	AP0110	Sonu	25
12
13	Product ID	Product Name	HSN Code	Seller Name	Qty Sold
14	105	Mic Holder

 

Drag in All Columns.

Hlookup Formula

=Hlookup(G1,A1:E6,6,0)

	A	B	C	D	E	F	G
1	Region	East	West	North	South	Region	West
2	Q1 Sales	500	350	860	350	Total	1800
3	Q2 Sales	700	400	550	990
4	Q3 Sales	1100	650	770	950
5	Q4 Sales	500	400	200	600
6	Total	2800	1800	2380	2890

 

Iferror Formula

 

=Iferror(Hlookup(G1,A1:E6,6,0),”Not Found”)

 

	A	B	C	D	E	F	G
1	Region	East	West	North	South	Region	Eastwest
2	Q1 Sales	500	350	860	350	Total	Not Found
3	Q2 Sales	700	400	550	990
4	Q3 Sales	1100	650	770	950
5	Q4 Sales	500	400	200	600
6	Total	2800	1800	2380	2890

 

Hlookup Formula Apply in Multiple Rows

 

=HLOOKUP($G$1,$A$1:$E$6,ROWS($F$1:F2),0)

 

	A	B	C	D	E	F	G
1	Region	East	West	North	South	Region	East
2	Q1 Sales	500	350	860	350	Q1 Sales	500
3	Q2 Sales	700	400	550	990	Q2 Sales	700
4	Q3 Sales	1100	650	770	950	Q3 Sales	1100
5	Q4 Sales	500	400	200	600	Q4 Sales	500
6	Total	2800	1800	2380	2890	Total	2800

 

Drag in All Rows.

& Formula

="Mr. "&A1&" "&B1

	A	B	C	D
1	First Name	Last Name	Gender
2	Raj	Singh	Male	Mr. Raj Singh
3	Rohit	Sharma	Male
4	Suresh	Sharma	Male
5	Anil	Kumar	Male
6	Reena	Kumari	Female
7	Anjali	Rajpoot	Female
8	Deepak	Nehra	Male
9	Neha	Sharma	Female

 

 

Double Vlookup Function

=VLOOKUP(VLOOKUP(A2,A1:C6,2,FALSE),A1:D6,3,FALSE)

	A	B	C
1	ID	Name	Salary
2	105	36000

 

 

 

 

	Sheet 1
	A	B	C
1	ID	Name	Age
2	101	Aman	25
3	102	Deepak	32
4	103	Priya	27
5	104	Anjali	33
6	105	Rohit	32

 

 

 

 

 

 

 

 

 

	Sheet 2
	A	B	C	D
1	Name	Post	Salary	Location
2	Priya	Manager	30000	Delhi
3	Anjali	Accounting	80000	Mumbai
4	Rohit	Ass.Manager	36000	Goa
5	Aman	Manager	32000	Delhi
6	Deepak	Ass.Manager	24000	Gujrat