As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

[Excel] Do I need a lookup formula or code?

1ddqd1ddqd Registered User regular
edited February 2010 in Help / Advice Forum
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):

rawdata.jpg

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:

assignments.jpg

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:

summary.jpg

For all intents and purposes, consider each column displayed here in each image "A" and each row as "1"

Thanks!

1ddqd on

Posts

  • mspencermspencer PAX [ENFORCER] Council Bluffs, IARegistered User regular
    edited February 2010
    The VLOOKUP() function does exactly what you're looking for. If you check the help page for VLOOKUP and you still have questions about how to use it, come back and post.

    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.

    mspencer on
    MEMBER OF THE PARANOIA GM GUILD
    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 )
  • 1ddqd1ddqd Registered User regular
    edited February 2010
    Ok, so I got it to work how I want it to, but I'm stuck with a different set of criteria (same layout though).

    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?

    1ddqd on
  • InfidelInfidel Heretic Registered User regular
    edited February 2010
    If I'm understanding you right, you'll probably need to make a hidden column with the two digits you want as an intermediate, and lookup with that.

    Infidel on
    OrokosPA.png
Sign In or Register to comment.