# Excel question, fairly simple (I hope)

Discussion in 'Business Applications' started by christianreese, Jun 10, 2009.

Not open for further replies.

Joined:
Jun 10, 2009
Messages:
2
So, here's the question. I am wanting to reference information from one worksheet into another. In worksheet 1 I have 2 columns. Each cell in column 1 has an a number (100, 109, 153, etc.) and column 2 has text which matches up with the number in column 1. So, A1 might say "115" and B1 would say "This number reflects auto information". In a second worksheet, I want to write in a number into column 1, and whichever number I write I need to refer to the corresponding number in worksheet 1, column 1. I then need it to write out the text to the right of the number I just input. So if I write "115" in A35 of the second worksheet, I need the corresponding text from worksheet 1 to write out in B35, "This number reflects auto information".

Basically, all the information is in Worksheet 1, and when I write out any of the numbers in Worksheet 2 I need it to be reflected. Make sense? Thanks everyone.

+ Christian

2. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Welcome to the board.

Are the sheets in the same workbook? If so, this formula in column B of the second sheet:

=INDEX(Sheet1!B:B,MATCH(A35,Sheet1!A:A,0))

Joined:
Jun 10, 2009
Messages:
2
That's great! Thanks Bomb #21. Not entirely sure what the formula means but it worked perfectly and I plan on deciphering it over the next few days. You wouldn't happen to have any references on books or websites where I could learn a lot of these functions and formulas? I know there are tons of websites available, but they tend to not be very user friendly and assume quite a bit of an excel working knowledge. Thanks again for the quick help!

+ Christian

4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
=MATCH(A35,Sheet1!A:A,0) returns the matching row number in column A of Sheet1 for the value in A35 (of Sheet2).

Say that number is (i.e. row) 20. Then the whole thing effectively becomes =INDEX(Sheet1!B:B,20) which is whatever is in row 20 in column B (of Sheet2).

K?

A fair few people who know a lot of these functions and formulas hang at this free and friendly website.

As Seen On