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
#2
Chad Sexington
Nevermind
I am a genius
#3
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.
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 ... CA | 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.
