Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two sets of data in excel

One has the account number, name, second one has account number and the bills

i would like to use the match function to create a table that would combine the two sets of data so that i can have one table that has the account number name and bill.

How do I write the function in excel?

share|improve this question
up vote 0 down vote accepted

I think you can achieve the desired result using Vlookup function instead.

As I understand you have two tables with one-to-one relations. For example:


Account Name
1       Bob
2       Ted
3       John


Account Bill
3       888
1       999
2       654

You can create a 3rd column in Sheet1 named "Bill" and use this formula to combine the two tables: =VLOOKUP(A2;Sheet2!A1:B3;2;0)

The result would be:

Account Name Bill
1       Bob  999
2       Ted  654
3       John 888
share|improve this answer

Vlookup was my go to formula for things like this, that is before someone pointed me to the INDEX/MATCH function.

It's a very powerful combination that doesn't require you to sort a list or insert additional search/sorting columns. You can find a very useful explination on the contextures website.

Your problem: if your set up is that you have your data on seperate sheets and the only lisason between BILLS and CUSTOMER is an account number

Sheet 1 alt text

Sheet2 alt text

In sheet 2 column C (Bill) - apologies for leaving off the column numbers, but hopefully you get my drift) you put the following formula: =INDEX(Sheet1!$B$2:$B$5,MATCH(A2,Sheet1!$A$2:$A$5,0))

This basically says: =INDEX(look in here and bring me the cell content/value from here,MATCH(where this cell value,is found in this list,and make it an exact match)) (the contextures website explains it better)

Anyway, the great thing about Index/MAtch is that it's powerful, and you don't need to sort your lists (as you can see from my example). also, it one of those things that you can build on and soon you'll be finding and retreiving things from all over the place. But it takes a little time to get your head around some of the things - but it's worth it.

..however, the VlookUp will be something you can use should you not feel comfortable with the Index/Match.

Hope it helps. Mike.

share|improve this answer
For some reason my sheet 2 has vanished. It had ACC but in a different, and Customer instead of bills. The Index/Match formula was in column C. – RocketGoal Aug 24 '10 at 16:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .