The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
[Excel] Do I need a lookup formula or code?
1ddqd
Registered User regular
Registered User regular
Guys, I come again to you with a question for the ages and it has to do with VLOOKUP/HLOOKUP
I never really learned how (or understood why) H/VLOOKUP works and I think it might be relevant for my problem.
I have an Excel sheet that implements a SQL query and dumps the 3 columns I'm looking for into a worksheet. That's easy. Here's a screenshot (important secret info hidden, but consider it a unique identifier):
So when I hit refresh (F5) the query runs and gives me this. I know how to make a pivot table, but only basically. What I want to do is have something look at the State (first column) and match it to a name here:
This list changes once a month and any manager might be assigned a different state to work, but this is largely a static list.
The results of the subtotaling/counting should populate in the yellow columns on this other worksheet:
For all intents and purposes, consider each column displayed here in each image "A" and each row as "1"
Thanks!
I never really learned how (or understood why) H/VLOOKUP works and I think it might be relevant for my problem.
I have an Excel sheet that implements a SQL query and dumps the 3 columns I'm looking for into a worksheet. That's easy. Here's a screenshot (important secret info hidden, but consider it a unique identifier):
So when I hit refresh (F5) the query runs and gives me this. I know how to make a pivot table, but only basically. What I want to do is have something look at the State (first column) and match it to a name here:
This list changes once a month and any manager might be assigned a different state to work, but this is largely a static list.
The results of the subtotaling/counting should populate in the yellow columns on this other worksheet:
For all intents and purposes, consider each column displayed here in each image "A" and each row as "1"
Thanks!
1ddqd on
0
Posts
Example: Make a new Excel spreadsheet.
Switch to sheet 2. Enter "A","B","C" down the first column, and "11","222","3333" down the second column.
Switch back to sheet 1. Enter "A","C","B" down the first column. In cell B2 enter:
=VLOOKUP(A1,Sheet2!$A$1:$B$3,2)
Then fill down. Note how the lookup range has $'s in it, to keep it from changing when you copy and paste the formula.
That formula will correctly fill in "11","3333","222" by looking the values up in the other tab.
XBL Michael Spencer || Wii 6007 6812 1605 7315 || PSN MichaelSpencerJr || Steam Michael_Spencer || Ham NOØK
QRZ || My last known GPS coordinates: FindU or APRS.fi (Car antenna feed line busted -- no ham radio for me X__X )
I want to look at the right-most last 2 digits of column B (on the data worksheet). However, it doesn't seem to read the range right when I do VLOOKUP; does it treat right() and left() differently than regular field lookups?