Using match function in excel

Question

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?

Answer 1

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

Answer 2

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

Sheet2

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.