Matching different sheets

[Ruba]

Hi, i have my itemised phone bill with all the numbers i used in one sheet. i have another sheet with the list of unique numbers and the names against them. firstly excel needs to match the numbers in the first sheet with the ones in the second sheet. then display the names against each number in the first sheet.


i'll be grateful if you can help me with this. thank you.

 

[Hui]

You can use an Index/Match combo


It will be in the format


=Index(Range2,Match(Ph_No, Column2, 0), x)

where:

Range 2 is the range on Sheet 2 with the unique Names and No.s

Column 2 is the column on Sheet 2 which has the Phone Number (should be the same length as range2)

Ph_No is the cell on Sheet 1 with the Phone number you are looking up

x is the column No of The Range2 which has the name in it

0 is zero 0

 

[Busymanjohn]

Poser ,,,, and really annoying me. I have a table of data consisting of the below


Geo/IOT Product Qtr Wk1 Wk2 Wk3 Wk4

1 Crabs 1Q09 1500 2000 2500 3000

1 Crabs 2Q09 2000 2500 3000 3500


This is only a small sample ,,, data goes on for hundreds of row. What I am looking to do is have the same data extracted form this "raw" data but only when it meets certain criteria ,,, such as Geo/IOT, Product, 1Q09 and Wk2 .... Geo/IOT and Wk number will be in a drop down menu ( combo box ), the others are content of cells .... I've tried using SUMPRODUCT, INDEX MATCH and VLOOKUP ,,, can't get any of them to work correctly ,,, been at it for a couple of hours and has become annoying. Any help please.

 

[Hui]

BushmanJohn, You should start a new post so as to not confuse or suffocate an in-work post.


Can you post your data somewhere

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

Do you want all records that match or summary data Sum(matching criteria) etc

 

[Ruba]

Thanks so much Hui. i've tried but it throws a NAME error. dont know why.

 

[Luke M]

Ruba,


Sounds like there's a typo/syntax error. Can you post an exact copy of the formula?

 

[Ruba]

Hi Luke, the formula i used is:


=INDEX(Sheet2!A$1:B$67,MATCH(D4,Sheet2!$A$1:$A$67,0),B)


thank you

 

[Hui]

Change the B at the end to a 1 for column A or 2 for column B

 

[Ruba]

WOW. it amazed me. Thanks so much Hui and Chandoo. i'm kind of experimenting with Xcel. With this kind of response, am certain to get back to u with more queries.

 

[Hui]

Ruba

Your B at the end of Index was correct, but in the wrong format.

Index just needs the column number from your range, not the column Name. So in your case A=1 & B=2