Posted on

INDEX and MATCH functions in Excel

I do a lot of work in Excel and I’ve used the INDEX and MATCH functions together instead of a VLOOKUP or HLOOKUP but always stumble through that process. I wanted to write a simple guide to help me in the future.

ID Item Color
1 Shoes Black
2 Socks Brown
3 Pants Blue
4 Shirts Red

Pretending for a minute that is our spreadsheet, let’s look at the functions in turn.

INDEX

Here is the function syntax: INDEX(array, row_num, [column_num]). Here is how I think of it and use it: INDEX(some rectangular shaped data set in excel, row number, column number)

Using the example, =INDEX(A1:C5,2,3) would return “Black” because that’s the data in the second row and third column of the rectangular shaped data set A1:c5.

MATCH

Here is the function syntax: MATCH(lookup_value, lookup_array, [match_type]) Here is how I think of it and use it: MATCH(what are you looking for, what rectangle of data should we search?, what kind of match are you looking for?)

Using the example, =MATCH(“Socks”,B1:B5,FALSE) would return “3” because “Socks” is in the 3rd column of that array.

CC image - https://flic.kr/p/nK7kRW
CC image – https://flic.kr/p/nK7kRW

INDEX and MATCH together!

Those functions are pretty useless on their own…but together they are awesome. Let’s say I want to know the color of the item with an ID of “4” – using these functions I can retrieve that data!

=INDEX(the column that has the information I want, MATCH(thing I’m looking for, column that has the data I’m starting with, FALSE))

Using the example, =INDEX(C1:C5,MATCH(“Shoes”,B1:B5)) would return “Black” because that is the data point at the intersection of column C and the row that contains the data point shoes.

Question to help the process:

  • What column has the information you want?
  • What data are you starting with?
  • What column has the data you are starting with?
Posted on

Review – “What is a Data-Driven Academic Library”

Last week, I watched the webinar entitled “What is a data-driven academic library?” hosted by Library Journal and really, really enjoyed it. Like, best webinar ever.

I was too engrossed in the content (and busy tweeting the event) to take comprehensive notes. But here are some of my notes from the webinar as well as later reflections.

To be data driven means to use data to drive change.

I feel like this is the core of the presentation. To be data driven means to use the data libraries and library systems collect to drive decision making. She proposed a five part method to accomplish this goal.

1. Create Questions

All questions are good – but bigger questions might need to be broken down into smaller chucks. Good questions are ones that are answerable based on data. To me, that meant questions like “are the nursing databases meeting current needs” must be translated into questions about usage and coverage – and that we should be honest and clear about that work. She also advised to not be overly reductive and to pay attention to other factors. Lastly, she advised people to let these questions guide your research – don’t get lost in the data!

2. Create a Plan

Along with crafting questions, Tudesco suggesting creating a plan. Essentially, what data will answer your question and how will you get that data? Refine your questions and create a plan or timetable for this project.

3. Collect and Manage Data

Honestly, I feel like I usually begin my data-driven projects at this step – and my projects almost always suffer because of this! My workflow usually begins with “What can I do with this data?” rather than the more important “How can this data help me make a decision?”. Allowing the question and project drive the data needs – and not the other way around – is a very valuable lesson I will take from this webinar.

She was also clear that sometimes the data you want/need doesn’t exist and that perhaps you need to create a tool to gather that data. Whether than means a survey or structured observations will be guided by the question

4. Analyze the Data

Again, this methodology focused on using the data to make a decision. This data analysis will bring together a lot of information – perhaps from different sources and with different nuances – and this will require interpretation and analysis to make sense of this. More on this later.

5. Make a Decision

This is where all that data matters! It’s very basic but it’s worth repeating – Data Driven libraries use data to drive decisions.

What Skills and Tools are Needed?

Tudesco mentioned three core skills library staff need to truly embrace the data movement and mentioned several tools under each area. This section also provided my favorite quote – one near and dear to my heart!

I’m trying to remember what she presented the best I can while also taking the liberty to add my own skills and tools to this list. This is a work in progress and will likely add more in the future.

Data Capture

  • Programming and database skills (SQL, etc.)
  • Circulation
  • Business domain knowledge
  • COUNTER reports and SUSHI protocols
  • Designing and implementing surveys

Data Analysis

Presentation

  • Visualization
  • Storytelling
  • Raw

Overall, this was a wonderful and timely webinar. I will do my best to take the lessons and tips to heart and structure all future data project in light of what this presentation taught me.

Here is the link to the webinar – I believe that if you register you will get archived access to webinar so you can watch it yourself!