Book of Engineering

Search
Skip to content
  • YOUR…
    • LOGIN
    • 🔖BOOKMARKS
    • FEEDBACK/REQUEST
  • MODULES
    • ME
    • EE
    • BP
    • CE
    • CHEM
    • CAL
    • MATH
    • CSE
  • TEA BREAKS
    • GOOGLE SHEETS
    • Duino-Coin ᕲ
    • EXCEL
    • C++
    • NANOCAD
    • FUCHSIA OS SDK
    • BLOG
    • VBA
EXCEL, TEA BREAKS

TEA BREAK WITH EXCEL: ALTERNATIVE TO VLOOKUP/HLOOKUP, INDEX(MATCH())

Book Of Engineering
Bookmark

Please login to bookmark

No account yet? Register

TEA RATING: ☕

If you are struggling to query your date in an excel sheet, you can use index(match()) formula to lookup. Unlike vlookup or hlookup, index(match()) is more flexible and more roboust.

Let’s start with this situation, you have a group of product prices in a list from different months.

As you can gues we will enter the product name we want to have a look at G1 cell, month to G2 cell and get the results for different months at G3.

So the formula works like this;

INDEX(<RANGE FOR YOUR RETURN DATA>;MATCH(<CELL TO BE LOOKED UP>;<WHERE TO SEARCH THAT CELL>;<“0” FOR EXACT MATCH>);<COLUMN THAT YOU WANT TO RETURN FROM THE RANGE>)

For this case will be like here;

=INDEX(B2:D4;MATCH(G1;A2:A4;0);G2)

So if you try with product “A”, in month “2” which is feb, (here user actually entering a column); then you will get 2 as a result;

Please also be aware that you may not enter any value to G2 which is month and you will get all the date responding to product “A” like below;

So it is also possible to return multiple date with this great tool.

Any suggestions or thoughts on how to improve The Book Of Engineering would be much appreciated. Please don't hesitate to send us an email or use our feedback form.

Send an email to info@bookofengineering.com

If you uncover any errors, mistakes, or otherwise inaccurate information, please let us know.

The Book Of Engineering material is copyrighted, however it is provided with NO WARRANTY or LIABILITY. It's highly advisable to double-check important information with other sources. All applicable national and local legislation, standards and practices must be strictly followed and adhered to in this regard.

Spread the love
alternativealternative to hlookupalternative to vlookupbig data excelcreate query in exceldatadata extraction tools exceldata from directorydirectoryexcelexcel big data queryexcel database queryexcel extract data based on criteriaexcel extract data from tableexcel extract data from table based on multiple criteriaexcel find named rangeexcel find rangeexcel find value in rangeexcel formula indexexcel get data from active directoryexcel index matchexcel match functionexcel query dataexcel query from rangeexcel transform dataexcel vlookup alternativeextract data from a cell in excelextract data from cell in excelextract data from email to excelextract data from excelextract data from excel fileextract data from excel sheet to anotherextract data from excel spreadsheetextract data from excel to excelextract data from multiple excel filesextract data from multiple sheets into oneextract data from multiple word documents to excelextract data from one spreadsheet to anotherextract data from spreadsheetextract data in excel based on criteriaextract specific data from excelformulafrom rangeget and transform data in excelget and transform excelget dataget multiple data from a rangehlookuphlookup and index matchhlookup index matchhlookup with index matchindex and match in excelindex function excelindex matchindex match formulaindex match formula in excelindex match function in excelindex match smartsheetmicrosoft query for excelms officemultiple datamultiple data returnmultiple querypower query active directoryquery a rangerangerange returnretunrreturnvlookupvlookup and index matchvlookup index matchvlookup with index matchvlookupmatch

Post navigation

Previous PostEE15: DEFINITION OF IP PROTECTION DEGREESNext PostEE16: PERMISSIBLE LOADING OF COPPER MAIN BUSBARS AND SECTION BUSBARS OF RECTANGULAR CROSS SECTION

Online Engineering Reference Book

This website or its third party tools use cookies which are necessary to its functioning. By scrolling this page, clicking a link or continuing to browse otherwise, you agree the use of cookies. Cookie Policy

🚶FOLLOW US ON

  • Facebook
  • Twitter
  • Flipboard

Privacy Policy / ABOUT US bookofengineering.com © 2016-2023
 

Loading Comments...
 

You must be logged in to post a comment.