Index Match Formula Help

beckyb27

Newbie struggling with a formula. I have two sheets. In the main sheet the Intake Number is auto populated. In the second sheet, staff will input the Intake Number- the intake numbers may not be in consecutive order on this sheet as a result like they are on the main sheet. They will also provide notes on the case (column titled: Investigator Updates) or attachments.

I want to create a formula that matches the Intake Number in both sheets and then returns the Investigator Updates to the main sheet on the correct row.

Everything I am trying with Match and Index isn't giving me anything!

(and can I return attachments to the main sheet?- what would I do for that?)

Main Sheet

Sheet 2

Genevieve P.

Hi @beckyb27

Can you post screen captures of how you're setting up the Index(Match formula (what columns are you selecting)?

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}, 0))

In your instance, you'll need to make sure that 0 is there at the end of the MATCH function because it identifies that the values aren't sorted in the other sheet.

So, it would be something like this:

=INDEX({Investigator Updates Sheet 2}, MATCH([Intake Number]@row, {Intake Number Sheet 2}, 0))

Let me know if this is returning an error or an incorrect result!

Cheers,

Genevieve

beckyb27

Thank you so much! Your guidance got me there! I got it to work with this formula:

=IFERROR(INDEX({FY22 Hotline -Updates Range 1}, MATCH([Intake Number]@row, {FY22 Hotline -Updates Range 2}, 0)), "")

Genevieve P.

Wonderful! I'm glad I could help 🙂

Jake Gustafson

@Genevieve P. how is the INDEX + MATCH function combination better to use than VLOOKUP? I'm trying to get a better understanding of how/when to use INDEX/MATCH over VLOOKUP and why.

Essentially, wouldn't the following VLOOKUP do the same as @beckyb27's formula above? @beckyb27 is there any reason why you went the route you did over VLOOKUP?

=IFERROR(INDEX([Intake Number]@row,{FY Hotline -Updates Range 3}, 3, FALSE),"N/A")

Where

• [Intake Number]@row is the Intake Number you are looking for any Investigator Notes in the other Sheet for
• {FY Hotline -Updates Range 3} is the reference to Sheet 2 where those notes are added
• 3 is not the right column number to reference because there appears to be hidden columns, but the screen shot makes it look like it's the third column.

Then the returned value would be the Investigator Update values corresponding to the Intake Number.

Thanks for whatever bits of knowledge can be dropped on me here to expand my knowledge on this area of functions.

Genevieve P.

Hi @Jake Gustafson

You could use a VLOOKUP, you're correct!

However I personally prefer INDEX(MATCH because it evaluates the two columns separately.

In a VLOOKUP you put in one range that spans across all your columns, with the first column in the range being the one with the Match and the last column (or one of the columns) being the value to return. Then you identify what column in that range you want returned.

So, in the instance where these columns are next to each other (therefore your VLOOKUP range is only 2 columns), then VLOOKUP and INDEX(MATCH would be equal in compatibility.

However, think about if you have a sheet where your Return Value and your Match Value are in columns that are quite far apart... for example 10 columns apart. Your VLOOKUP range would have to select all those columns in one go and could potentially break if someone edited the source sheet and moved columns around. As your sheet gets more complex, having a large range selected where a formula has to evaluate all those additional columns could slow it down.

This is where INDEX(MATCH is more flexible: you select the column to Return in the INDEX function, then the MATCH function uses the Column with the Matching Value to identify the row to bring back. Since you're referencing each column individually, you can move them around in the source sheet and the Index(Match will follow each column as it moves. It also eliminates any of those in-between columns from needing to be read through, which will keep your referenced cells to a lower number and you'll be less likely to hit reference cell limits.

Does that help clarify? I'd be happy to post screen captures to explain what I mean about the ranges, if that would be useful.

Cheers!

Genevieve

Jake Gustafson

Thank you @Genevieve P. that clarifies things much better than what I was understanding before. That's where the efficiency piece comes in to avoid processing across multiple columns. Thanks. I will try to work that into my repertoire to get more comfortable with it.

Genevieve P.

No problem 🙂

Nancy Skoulphong

Hi @Genevieve P. Along with the same question, can you help take a look at this formula? I am getting an #incorrect argument set. Let me know if you need additional information. Thank you in advance for your help.

=IFERROR(INDEX({RichardWallach}, MATCH({RichardAppNo}, [Applicant Number]@row), 0), "")

I am collecting a score from Richard's sheet and populating it onto Sheet A. If the Application Number on Richard's sheet match with the application number on Sheet A, populate the score on Sheet A. If no match, the result should be a blank. The property on all the columns is Text/Number.

Genevieve P.

Hi @Nancy Skoulphong

A couple of details here in the MATCH function that just need to be swapped around.

Firstly, you want to list the Value and then the Range: [Applicant Number]@row before {RichardAppNo}, like so:

MATCH([Applicant Number]@row, {RichardAppNo}

Then secondly, the 0 should be part of the MATCH function, so inside the ) instead of outside, like so:

MATCH([Applicant Number]@row, {RichardAppNo}, 0)

Try the full formula:

=IFERROR(INDEX({RichardWallach}, MATCH([Applicant Number]@row, {RichardAppNo}, 0)), "")

Let me know if that works for you!

Cheers,

Genevieve

Nancy Skoulphong

Hi @Genevieve P. You are awesome! Thank you.