Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

[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 Registered 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.

    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?

  • 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.

    TwitchTV channel: OrokosPA OrokosPA
    Play D&D 4e? :: Check out Orokos and upload your Character Builder sheet! :: Orokos Dice Roller
    The PhalLounge :: Chat board for Critical Failures IRC! :: #CriticalFailures and #mafia on irc.slashnet.org
Sign In or Register to comment.