[Question] EXCEL! Why do simple tasks seem impossible

[Chad Sexington]

NEVERMIND. FIGURED IT OUT. Read on if you're curious about what I couldn't do.

I feel like I do this a lot. Like, maybe my whole purpose is to post excel questions.

Sigh.

I have two Worksheets, Sheet1 and Sheet3

Sheet1 has a list of 686 numbers, all in column A. In Column C, it has a corresponding number that is meaningful.

Sheet 3 has shorter list of numbers, important ones weeded out from the 686.

I want to tell Excel that Sheet3!A*=Sheet1!A*, put the Sheet1!C* value into Sheet3!B*

(I use the asterisks as wildcards for example purposes in this email. I know they don't do that in Excel)

Sheet1!A* and Sheet1!C* will both have the same number *

Likewise for Sheet3!A* and Sheet3!B*

The closest I've come is
=IF(MATCH(A2,Sheet1!A:A,0),Sheet1!C33, FALSE)

But you can see the obvious problem! C33 is based on where Sheet1A:A, 1) match; it won't consistently be C33. How do I tell that to change but to be consistent with where Sheet1!A:A matches Sheet3!A:A? (incrementing A2 happens automatically when the formula is filled down, as I imagine you know, so that is not a problem).

Sigh

If you have nothing better to do

 

[Chad Sexington]

Nevermind

I am a genius

 

[jwhouk]

Sorry, I don't do too much cross-sheet references. But, it sounds like you just needed to go =$sheet1!A* in sheet 3.

 

[Chad Sexington]

Sorry, I don't do too much cross-sheet references. But, it sounds like you just needed to go =$sheet1!A* in sheet 3.

No, see, A* from Sheet1 matched with A* in Sheet3 - I wanted sheet1's corresponding number in C

Here's a sloppy version of what I meant:

Sheet1 Sheet3
A ... C A | B
1| 2001| 4 1| 2002 |<formula that grab value in Sheet1!C* where Sheet3!A1=Sheet1A*>
2| 2002| 5 2| 2005 |<formula that grab value in Sheet1!C* where Sheet3!A1=Sheet1A*>
3| 2003| 18
4| 2004| 456
5| 2005| 1


Anyway, INDEX MATCH solved my problem easily and beautifully.