Deleted
Posts: 0
Oct 9, 2024 22:18:43 GMT
|
Post by Deleted on Aug 19, 2014 22:06:04 GMT
Here's my scenario:
In columns A & B, I have the following:
A 1234 A 5678 A 9098 B 9876 B 8765 B 7654 C 6543 C 5432 C 4321
I then have this list, say in other worksheet:
A xxxx B xxxx C xxxx
I need to write a formula that will look at the item in the first column, from my second worksheet, and find the corresponding item on the first worksheet, then bring me back the first item from column B...all those in red.
Anyone have a clue?
|
|
|
Post by ljsmom on Aug 19, 2014 22:13:48 GMT
I'm not sure I'm totally clear on what you need but you likely want to use Vlookup I think...
|
|
|
Post by redshoes on Aug 19, 2014 22:18:50 GMT
Have you tried the INDEX function?
|
|
Deleted
Posts: 0
Oct 9, 2024 22:18:43 GMT
|
Post by Deleted on Aug 19, 2014 22:33:09 GMT
redshoes: I haven't tried INDEX function, never used it. I'll google on that.
ljsmom: I played around with Vlookup without success. I want to bring back in my second worksheet each number in red for the corresponding letter.
So for "A" - I want to bring back 1234
|
|
|
Post by momofkandn on Aug 19, 2014 22:42:20 GMT
You can use vlookup. But the results will be thrown off if there are multiple corresponding values it's looking up. In your first worksheet you need to make sure there is only one value for A for instance. There are some good tutorials if you google vlookup
|
|
|
Post by ljsmom on Aug 19, 2014 22:43:24 GMT
you want it to bring back 1234 but there are multiple rows that have "A" (and "b" and "C") as identifiers in the first column?
|
|
|
Post by ljsmom on Aug 19, 2014 22:50:33 GMT
I got a vlookup to work duplicating the little table you have above. For A it brought back 1234, B it brought back 9876 and C 6543 If your table is columns A & B rows 1-9 here is the formula for the look up item I just started it with A B C in column A (rows 13-15) added the $ so I could just copy it down. Sorry if this makes no sense. Is getting late in my day and I'm over spreadsheets =VLOOKUP(A13,$A$1:$B$9,2,FALSE)
|
|
Deleted
Posts: 0
Oct 9, 2024 22:18:43 GMT
|
Post by Deleted on Aug 19, 2014 23:18:57 GMT
You can use vlookup. But the results will be thrown off if there are multiple corresponding values it's looking up. In your first worksheet you need to make sure there is only one value for A for instance. There are some good tutorials if you google vlookup The only problem is that there are 10's of thousands of rows. We can't go back and remove the dups. I think we might have a chance with the Vlookup formula above. ljsmom: Yep, that's what I just tried as well. By choosing FALSE, it grabs the first row for A. If you choose TRUE, it gives you the last item for A.
|
|
Deleted
Posts: 0
Oct 9, 2024 22:18:43 GMT
|
Post by Deleted on Aug 19, 2014 23:36:09 GMT
I love excel and use it often. Every time I see one of these threads, I open it, thinking I can answer it. Then I realize how very much I am NOT an excel expert. You ladies amaze me.
|
|
|
Post by momofkandn on Aug 19, 2014 23:56:16 GMT
If you consistently want the value next to the first instance of the value you are looking up, then using false will work. But if you need the second or third instance you'll have to get rid of the extra data. You can also try the remove duplicates function. But again that will keep the first instance and remove subsequent ones.
|
|
|
Post by BuckeyeSandy on Aug 20, 2014 0:12:38 GMT
You CAN copy and paste the formula, OR if it is several in the same column or line you can copy the formula into the ENTIRE RANGE. You paste the formula into the first block cell, and then do a drag (with the block highlighted in the lower right corner, that little square, cursor over it, right click and drag.)
(There is another name for it but I am tired today from other stuff I was doing.)
|
|
|
Post by kelbel827 on Aug 20, 2014 0:23:56 GMT
I'm thinking the same thing as Jen.. I suck at excel. How can I learn all this fun fancy stuff?
|
|
|
Post by ljsmom on Aug 20, 2014 1:02:43 GMT
I'm thinking the same thing as Jen.. I suck at excel. How can I learn all this fun fancy stuff? I have learned much of it bc I am lazy and want to find a way to do things more quickly. keep at it. Play with it look at the diff formula types and conditional formatting options and figure out ways it can apply to what you need.
|
|
|
Post by BuckeyeSandy on Aug 20, 2014 4:24:47 GMT
Microsoft Office Specialist (MOS) For those that want to learn MORE about various Office suite software, you can get training (self-paced or attend a class) and take an exam for a certification. I used to instruct on software applications. Once you get good on one version, it is not too difficult to add newer versions to your expertise.
|
|