Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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:

Sheet1:

Account Name
1       Bob
2       Ted
3       John

Sheet2:

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.