VLOOKUP and HLOOKUP take up a lot of memory and processing power, especially if you have a large data set. A great alternative to VLOOKUPs and HLOOKUPs are the INDEX & MATCH functions. Below is an example from Mr. Excel.

## The Problem

One day, you have a situation where you have the employee name, but need the employee number. In the image above, you have a name in A10 and need to find the employee number in B10.

When the key field is to the right of the data you want to retrieve, VLOOKUP will not work. If only the VLOOKUP would accept -1 as the column number, there would be no problem. But, it doesn’t. One common solution is to temporarily insert a new column A, copy the column of names to the new column A, populate with VLOOKUP, Paste Special Values, then delete the temporary column A. Excel pros can probably do this move in their sleep.

I am going to suggest you take the challenge and try to use this single step method. Yes, you will have to tack the formula up on your wall for a few weeks, but you did that with VLOOKUP a long time ago, too, didn’t you?

I think the reason this is so difficult is that you are using two functions which you probably never used before. So, let me break it down into two pieces.

## The Solution: INDEX & MATCH

First, there is the INDEX() function. This is a horribly named function. When someone says “index”, it does not conjure up anything in my mind that is similar to what this function does. Index requires three arguments.

=INDEX(data range, row number, column number)

In English, Excel goes to the data range and returns you the value in the intersection of the (row number)th row and the (column number)th column. Hey, think about it – this is pretty simple, right?Â =INDEX($A$2:$C$6,4,2)Â will give you the value in B5.

Applying INDEX() to our problem, you can figure that to return the employee number from the range, you would use this:Â =INDEX($A$2:$A$6,?,1). Actually, this piece of it seems so trivial that it seems useless. But, when you replace the question mark with a MATCH() function, you have the solution.

Here is the syntax:

=MATCH(Value, Single-column data range, FALSE)

It tells Excel, “Search the data range and tell me the relative row number where you find a match for (data). So, to find which row has the employee in A10, you would useÂ =MATCH(A10,$B$2:$B$6,FALSE). Yes, this is more complex than Index, but it should be right up the alley of VLOOKUP pros. If A10 contains “Miller, Bob” then this MATCH will return that he is in the 3rd row of the range B2:B6.

There it is – the MATCH() function tells the Index function which row to look in – you are done. Take the Index function, replace our question mark with the MATCH function, and you can now do the equivalent of VLOOKUPs when the key field is not in the left column. Here is the function to use:

=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)

The sticky note on my wall actually shows it as two lines. First I wrote out the explanation for MATCH(). Below that I wrote the explanation for INDEX(). I then drew a funnel shape between the two to indicate that the MATCH() function drops in to the 2nd argument of the INDEX() function.

The first few times I had to do one of these, I was tempted just to slam a new temporary column A in there, but went through the pain of doing it this way instead. It is faster, and requires less manipulation. So, the next time you are wishing you could put a negative number in the VLOOKUP function, try this strange combination of INDEX and MATCH to solve your problems.

## 38 Comments

Hey Karen,

When I use the Index/Match formula on a 3000 rows table it freezes my excel and has that % increasing over an hour, and eventually it calculates, but whenever I toutch the table it does it again…

Can this be solved?

Hi Zaf! There is definitely a way to make Excel stop calculating all formulas automatically. You can see the instructions here: http://howtouseexcel.net/how-to-make-excel-stop-calculating-automatically

Super !

I had trouble getting VLOOKUP to work (For unknown reasons) but the INDEX+MATCH got the job done.

VLOOKUP didnt find all the data for some reason…

/K

Hi Klas! That’s strange. VLOOKUP and INDEX+MATCH should work in the exact same way! I am happy INDEX+MATCH worked for you though!

Hello,

I need to do a vlookup from one column which then enters this data to another form (its a customs delaration for a vessel)

I am using a persons onboard sheet as reference. Each person is allocated a cabin but not all beds are always used so there will be space in some cells.

I only want the data entered for the beds used and need to bypass the empty beds.

Basically on the declaration I want to omit blank lines so the declaration looks a little more professional.

Thoughts appreciated..

Best regards

Mark

Hi Mark! That does sound like a problem that VLOOKUP or INDEX & MATCH can easily solve! Have you tried using the formula above?

Hi,

here you given formula for sinle row. but i need

in one excel following

Emp.# Name Dept

a204 smith, jo Accounting

a206 doe, jane Operations

a208 miller, bob MID

a210 white, fred Accounting

a212 young, jill Manufacturing

a212 young, jill Accounting

in another

smith, jo

doe, jane

miller, bob

young, jill

young, jill

how to retrieve multiple result for young, jill.

Hi Muthukumar, you are right, VLOOKUP and INDEX & MATCH will only return the first result they find. In your example above, you have two instances of “young, jill”. VLOOKUP and INDEX & MATCH will only return the first result, which is “Manufacturing”. Do you have unique ID’s for each of these rows? Whenever you create a table, it’s always useful to have a unique identifier for each row. That way, even when there are duplicates, you can still easily do a match.

small typo – might confuse some newbies…

you have a name in A10 and need to find the employee number in B10.

Hi Kevin, that’s exactly what I meant — if you have a name in A10 and you want to write a formula in B10 to return the employee number that matches the name in A10.

How do i pick a vertical data in one sheet into another sheet and to be horizontally arranged. That is, vertical to horizontal without the use of tranpose?

Hi Sholeye! You can use HLOOKUP instead of VLOOKUP. Have you tried that? “H” in HLOOKUP stands for horizontal and “V” in VLOOKUP stands for vertical ðŸ™‚

Hi, I would like to enquire about some issues regarding excel. I encountered some problems where I would have to extract information from four different database and then do V-Lookup to consolidate them into one spreadsheet. My team is trying to find an alternate solution which is less tedious. Do you have any suggestions regarding this issue? Thank you for your help!!!

Hi Shihao! I understand that you want to consolidate information from four different tables into one. The easiest way to do to this would be to use either the VLOOKUP or the INDEX & MATCH formula described in this article. Have you tried these formulas?

Is there any way to make the function only look up information on data that is entered. I have used the index/match in a row. So if first name and last name equal, specific information in entered from another worksheet to a column. Every time I do this now, it freezes my computer. Also, I will copy and paste some of the data to make reports, it tries to link the copied information to the original. I only want the function to work on the original sheet and not update my reports.

Thanks

Hi Fran! I want to make sure that I understood your question. I understand that you do NOT want your workbook to recalculate automatically because it causes your computer to freeze. Is that right? I have a tutorial here on how to make Excel stop calculating automatically: http://howtouseexcel.net/how-to-make-excel-stop-calculating-automatically Hope this helps, Fran!

If you are manually copy/pasting then why not try ‘Paste Special’ (found in the Right click menu) and the select ‘Values’. This just pastes the displayed values and not the formulas/links.

I am interested to know how I can use index and match for multiple criteria using formula.

a) to identify for each cow time that moves On and Off each site (3, 4 or 5 as in example).

I’ve given each record an identifier but may need to change this- she can revisit sites (as in the example). Also need to count the number of times the site is shown in the column i.e. 9 times for site 4 for cow A.

Want to try and get something that I can download data into and then it self computes my data rather than doing this manually. What would be a good approach to use using avariety of formulae like index match, vlookup etc. Any help appreciated…thanks

Cow identifier code unit timer

A 1 On 0:00:03

A 1 4 0:00:04

A 1 Off 0:00:06

A 2 On 0:00:09

A 2 5 0:00:10

A 2 5 0:00:15

A 2 5 0:00:45

A 2 5 0:01:45

A 2 5 0:02:32

A 2 Off 0:03:50

A 3 On 0:03:52

A 3 4 0:03:58

A 3 4 0:04:04

A 3 4 0:04:10

A 3 4 0:04:15

A 3 4 0:04:26

A 3 4 0:04:34

A 3 4 0:04:38

A 3 4 0:04:45

A 3 Off 0:05:00

B 4 On 0:05:02

B 4 4 0:05:03

B 4 Off 0:05:10

B 5 On 0:05:15

B 5 3 0:05:45

B 5 3 0:06:05

B 5 Off 0:06:45

Hi Wendy! Have you tried to use a combination of the CONCATENATE and the VLOOKUP (or INDEX & MATCH functions)? For example, you can concatenate the first two (or three) columns and then do a lookup afterwards.

Very helpful function! I just used it to replace an ugly control table I was using to copy data from a pivot table to another table just to sequence it to perform a vlookup. While I can appreciate the limitations other mentioned this is far better than what I was doing.

Hi Marc! I am so happy to hear that I was able to help ðŸ™‚

Thanks for the tip, worked perfect.

I have used Vlookup/Hlookup for long and this Index/Match solves some of my concerns, mainly the sorting issue with said functions. I changed the “column number” argument in Index from “1” to a user variable so the Index/Match searches an entire table.

Hi Alejandro! Glad to hear that the tip worked ðŸ™‚

Thanks a lot this was so much useful

You’re very welcome Raj! ðŸ™‚

Hi Karen – Can this work if my data is on one worksheet but my Index-Match equation is on a different worksheet? The equation seems to get confused when I try to specify the column number for the index function. It defaults to the column number of the worksheet where my equation resides not the column number of the worksheet where my data resides.

Hi Tom! This formula definitely works across worksheets. Just make sure to reference the correct worksheet in the formula. The easiest way to do that is to just highlight the entire column you’re indexing on another worksheet. Let me know in case it doesn’t work!

That’s amazing. Another limitation is that we cannot retreive multiple values if they are lined up in same column against one reference. As vlookup gives first entry only. Is there any other function where I could get multiple entries against same reference?

I see what you mean! Unfortunately, even INDEX & MATCH also only returns the first entry. I don’t know of any Excel formula that can retrieve multiple values, but I know that you can use a SQL query to do that.

WHAT CAN I DO TO LOOKUP WHERE THE LOOKUP VALUE REPEATS????

Hi Gokuldas! What do you mean by the “LOOKUP value repeats”? Can you describe it in more detail, so I can help you?

Thank you Karen, this little tip is very helpful!

You’re welcome Antony! Glad to hear I was able to help ðŸ™‚

This is perfect!! The limitation of VLookUp is that you need to have columns in an order but with this you can reference to any column! ðŸ™‚

Glad to hear you found INDEX & MATCH helpful. You are right, that’s another benefit in addition to the performance benefits of using this as an alternative to VLOOKUP.

look for “GETIF() advanced” or “GETIF() v3.00”, in the latest form.

It uses VBA. Read comments to uncover some possibilities.

don’t misunderstand me, I do not say things cannot be done without this function, I am just saying that even novices can use GETIF() to get array of matches without need to become experts in Match,Index,…Lookup etc

Usage in conjunction with offset() is uplifting

This is a wonderful tip. I was having a headache with Vlookup and a very large list. Your post saved my day. ðŸ™‚

Glad to hear the tip was helpful!