Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

INDEX & MATCH functions in a formula - how2 do I do this?

0

I have 2 sheets in a workbook "Sheet 1" and "Sheet 2" in a workbook.

In Sheet 2, column A contains ID#s & in B are territory names.

In Sheet 1 column A is blank. In Column M are ID#s and column O has territory names.

This is the formula I'm trying to use:

=INDEX(Sheet1!B:B,MATCH(M2,Sheet1!A:A,0))

I need the territory in Sheet 2 to be copied into the matching record Column A of Sheet 1 based on the ID#.

I did this a while ago but for some reason I'm unable to duplicate this.

Thanks for any help you can provide.

Seamus

Answer
Discuss

Answers

0

This formula should do the job.

=INDEX(Sheet2!$B:$B,MATCH($M2,Sheet2!$A:$A,0))

Just in case it doesn't, knowing how it works would enable you to tweak it. First the MATCH() function.

=MATCH($M2,Sheet2!$A:$A,0)
The instruction is to look for a[n exact] match of the value in M2 (of the sheet where the formula resides) in Sheet2's column A. Simple enough. You can actually put this in a cell in Sheet1 and it will return a number if a match is found.

The INDEX() function isn't any more difficult. It specifies a cell using row and column coordinates. For example, cell(15, 1) would be in the 15th row of the first column. All you need to know is where to start counting. Therefore the function provides that information first.

=INDEX(Sheet2!$B:$B,15)
You can put that in a worksheet cell, too (on Sheet1) and it will return the value of the 15th row of Sheet2!$B:$B. Note that the column number has been omitted. That is OK because the range $B:$B only has one column.

Now all you have to do is to replace the static 15 with the variable value provided by the MATCH() function and you arrive at the formula first above given.

If I understood your requirement correctly, the formula below should do the same job.

=VLOOKUP($M2,'Sheet2'!$A:$B,2,FALSE)
Discuss

Discussion

Thanks.  I may be confused about where this formula goes,   I want the territory from Sheet 2 to fill in the correct cell in Column A of Sheet 1 with tthe matching ID#.  So I should fill Column A with this formula, correct? 
Nothing happens, I just see the formula in each cell.  Do I have to "run" the formula? I'm using =INDEX(Sheet2!$B:$B,MATCH($M2,Sheet2!$A:$A,0))
seamus (rep: 4) Dec 29, '17 at 9:08 am
Per your instructions I've got the formula in A of Sheet 1 and Calculate is set to automatic.  I'm trying you idea of doing Match & Index separetly w/o success as of yet.  There has to be a step I'm missing somewhere.
seamus (rep: 4) Dec 29, '17 at 10:08 am
Thanks I got VLookup one to work
seamus (rep: 4) Dec 29, '17 at 2:36 pm
If you see the formula instead of the result the cell's format is set to "Text". From the Home tab, go to Format > Format Cells and select any other format from the Number tab. Then select the reformatted cell, press F2 and Enter, or do anything else to let Excel recalculate it.
If my answer solved your problem please select it as "Selected Answer" on the forum. Thank you.
Variatus (rep: 4889) Dec 29, '17 at 10:00 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login