• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching different sheets

R

Ruba

New Member
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.
 
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
 
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.
 
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,


Sounds like there's a typo/syntax error. Can you post an exact copy of the formula?
 
Hi Luke, the formula i used is:


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


thank you
 
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. :)
 
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
 
You must log in or register to reply here.
Back
Top