Top 5 LOOKUP Functions in Excel You Need to Know

LOOKUP Function VLOOKUP Function HLOOKUP Function XLOOKUP Function INDEX and MATCH

What Are LOOKUP Functions in Excel?

[{"selector":"#anim-288675c5-a484-4644-b5cd-f13e033848a3","keyframes":{"transform":["translate3d(-117.10526%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-4ee9cd04-fdb6-4aa7-bbef-93c50f9c8b9b","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-9cb08853-2bb3-4142-a6c3-82ba07536254","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-88597e3f-58dc-48ef-a57a-a89668faea73","keyframes":{"transform":["translate3d(-116.93811%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-717f97df-8869-469a-a275-b911cf716702","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-65f50d83-a3e3-4a46-8574-e068bbd8eb25","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] Excel's LOOKUP functions efficiently retrieve data based on criteria. These functions aid in the quick extraction of information from large databases and streamline data management.

LOOKUP Function

[{"selector":"#anim-4d4c37c6-8b9b-45ca-a48c-4279d03bac7f","keyframes":{"transform":["translate3d(-122.93234%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-3c33fea1-b6ee-401a-abe6-52e433168593","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-343ce8ea-dc5e-4e4b-b4d6-ac15783d2b4d","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-dfa0ea62-4be3-4a6b-b71a-ea699afd9742","keyframes":{"transform":["translate3d(-117.10526%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-85e7126d-902f-425a-b27a-252ab99f70c1","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-dda5efc4-aea2-4fde-9d37-4cb0d5412c30","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-7feb011b-e57a-498d-a201-536c9231074e","keyframes":{"transform":["translate3d(-116.93811%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-8a19f760-e307-4c08-8e0f-fcc16504d23a","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-0482ba3d-a5f5-4241-86f1-62e196918036","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-e12fe0c5-16f6-4b27-83a4-b634af62d714","keyframes":{"transform":["translate3d(-117.26385%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-2473947a-42a3-4914-a0a4-43e9fdc4a830","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-904445d7-c0f4-4008-a4a9-0ca8e2f0ba65","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] =LOOKUP(lookup_value, lookup_vector, [result_vector]) Excel’s LOOKUP function finds values in a range or array. It has two types: array and vector. Example: To find the region for the sales ID S001: Formula: =LOOKUP("S001", A1:A10, B1:B10)

VLOOKUP Function

[{"selector":"#anim-7815113e-bb0f-48a5-a987-745876b13e96","keyframes":{"transform":["translate3d(-118.95425%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-3d19c53d-d4d5-42b0-8e1d-34bf3726c373","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-3c73443f-641e-4657-bcab-2272337a6229","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-a3a1b605-c77c-4f69-96dd-df5d68cea0da","keyframes":{"transform":["translate3d(-117.10526%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-9b0600e3-9bf3-49fd-b848-89954cbfaca6","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-a82c5e5a-7279-4cb7-96a1-942e6fcd6006","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-e06b4e74-43af-4b27-8326-7d454fe74bbc","keyframes":{"transform":["translate3d(-116.93811%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-ae384b0d-b94a-4a14-a7eb-a8404bd4c18b","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-021a0ca6-4a5e-4d57-8fa5-451f2ae2554e","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-ce464af5-3e05-489d-804a-a9a4ada2d339","keyframes":{"transform":["translate3d(-117.26385%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-5e0c6d78-cba1-4c3c-8bbd-5a3458cbcfbd","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-03eff918-a7de-4c99-9a62-407231cd7c21","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Excel's most-used function, VLOOKUP, searches for a value in the first column of a range and retrieves a corresponding value from another column in the same row. Example: You have a list of employee IDs in column A and their corresponding salaries in column B. To find the salary of an employee with the ID E102: Formula: =VLOOKUP("E102", A1:B10, 2, FALSE)

HLOOKUP Function

[{"selector":"#anim-6b9eeecb-514f-4da8-a28d-31c701679cbc","keyframes":{"transform":["translate3d(-118.95425%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-8bc27578-1330-46d1-a08e-3f88f75764ec","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-efb08d27-ab31-46a0-ae00-414a46e62f42","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-116cc748-88cf-4e99-8aaa-f4d53445046f","keyframes":{"transform":["translate3d(-117.10526%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-7c720f42-1daf-4097-a6b8-24bcaa0a9184","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-a098a748-d91b-4049-9de3-62aa3f6201e3","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-e398c6b7-7564-4042-856c-05db65ade459","keyframes":{"transform":["translate3d(-116.93811%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-487421dc-5e98-41b4-9d63-1bda0d437aa2","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-0c8344de-6952-471d-9358-9db9d910c55c","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-5f08f116-3a2a-4e6c-98ec-1b325d3f635d","keyframes":{"transform":["translate3d(-117.26385%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-9e4619ff-a38d-481d-99b0-a0dc6d351d7a","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-fc385992-925e-49d2-bc2c-2a0e51f13e4b","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) The HLOOKUP function searches the top row of a range for a specified value and retrieves a value from a different row within the same column. Example: If you have a table with monthly sales data for different products and you want to find the sales for Product B in March: Formula: =HLOOKUP("Product B", A1:D5, 3, FALSE)

XLOOKUP Function

[{"selector":"#anim-7e9ac612-c50a-4633-a6e8-e466a0e3089b","keyframes":{"transform":["translate3d(-118.95425%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-4ad6d392-a86d-49d5-8ff2-1ed5e0876e88","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-0240873e-2fef-4100-b778-d4c071863865","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-38f5120d-2310-4835-a146-789f59295ab8","keyframes":{"transform":["translate3d(-117.10526%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-0919d385-8401-4d57-9606-a398b9c27a17","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-0a5a757c-a158-40de-86c6-1886ce20f2fa","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-1ad4b561-d179-414a-8b4c-d2f73a178a51","keyframes":{"transform":["translate3d(-116.93811%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-ad34a152-f78d-4ca0-a84d-7ce7873c941e","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-5bbe4737-fe99-4b5a-bf0b-cb381401cb5b","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-0b628bb6-7337-405b-a8b3-0bd8c086dd5b","keyframes":{"transform":["translate3d(-117.26385%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-30b73230-4167-4bc0-bd2c-bd57246b7745","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-0698e707-91ac-48e4-958f-a73154f61799","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) XLOOKUP was designed to replace VLOOKUP and HLOOKUP, offering enhanced features that make it more powerful and versatile. Example: To find the price of a product with code P202 across rows: Formula: =XLOOKUP("P202", A1:A10, B1:B10)

INDEX and MATCH

[{"selector":"#anim-9d4067ad-c9b1-4a98-8cce-a5475af69bd7","keyframes":{"transform":["translate3d(-118.95425%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-55c897cc-d1d3-497b-9968-e858d2b397e6","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-4264e601-ca5f-402e-b174-3bad943165a4","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-ec9cebb9-e045-4b5a-a6c0-d79343afd139","keyframes":{"transform":["translate3d(-117.10526%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-ed98c69e-32d6-4d17-a44b-04c087bf9b5d","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-523cf5c0-e61d-4cf1-a5a6-dbf828e5ba2c","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-3dfc3a35-d24e-4819-bb51-d48ca79794ed","keyframes":{"transform":["translate3d(-116.93811%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-55d0fc05-0cee-4a25-8d56-8548e360578d","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-6dcfde5c-6acf-4894-9f7a-65f10baaef1c","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] [{"selector":"#anim-9b61e23f-f425-4d4d-b122-4116058a222c","keyframes":{"transform":["translate3d(-117.26385%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-961af03f-6bd2-4cfb-b58f-6e732c577a7c","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-70600daa-052b-4752-9528-3c1965496e91","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}] =INDEX(return_range, MATCH(lookup_value, lookup_range, [match_type])) The INDEX and MATCH functions together offer an alternative to VLOOKUP and HLOOKUP, allowing you to search for values both horizontally and vertically, enhancing versatility in Excel. Example: To find the department of an employee named "John Doe" using the INDEX and MATCH functions: Formula: =INDEX(B1:B10, MATCH("John Doe", A1:A10, 0))