+ Reply to Thread
Results 1 to 6 of 6

Picking from Database

  1. #1
    Registered User
    Join Date
    11-29-2016
    Location
    Kansas City
    MS-Off Ver
    2007
    Posts
    2

    Picking from Database

    Here's what I want to do.

    Sheet A has a list of numbers between 1-99. These are listed in column A.
    Sheet B is a "database" where all numbers, 1-99, are listed in order in column A. Next to those numbers in column B are dollar values. 1 is next to $500, 2 is next to $750, and so forth.

    I want column B of sheet A to search the "database" and match up with the corresponding dollar amount.

    I know this may sound basic and I've dealt with way more complicated things in excel but I'm honestly a bit stumped and can't find the answer I want.

    Any help is appreciated.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Picking from Database

    a vlookup should do it, something like this... =VLOOKUP(A1,'Sheet B'!$a$1:$b$99,2,FALSE)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    11-29-2016
    Location
    Kansas City
    MS-Off Ver
    2007
    Posts
    2

    Re: Picking from Database

    Works. Does the 2 basically just look at the second column or...?

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Picking from Database

    yes, the 2 says that you return the value in the second column over from the first column (A in this case) and the false means return the exact value found.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Picking from Database

    thanks for the rep! BTW, (not sure of your skill level as we all are learning) but it can be rewritten this way too...
    =VLOOKUP($A1,'Sheet B'!$A$1:B$99,column(b$1),FALSE)
    with a formula like this it allows you to drag the formula to the right and index from column to column so you don't have to change the 2 to a 3 and so on. Changing the $B$99 to B$99 locks in the rows but not the columns and moves it to the right too. Just some little handy tricks.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Picking from Database

    FWIW...

    In the COLUMN/ROW functions:

    COLUMN(B$1)

    The row number is totally irrelevant so there's really no need to make the row absolute.

    ROW($B1)

    The column number is totally irrelevant so there's really no need to make the column absolute.

    However, doing so may seem more intuitive to some!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-22-2014, 11:32 PM
  2. Need macros to extract data from database/compare/update database - Excel 2007
    By saroby in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-15-2014, 01:45 PM
  3. Replies: 9
    Last Post: 10-22-2013, 04:20 PM
  4. [SOLVED] Remove one smaller database from larger, master customer database
    By BigJonF in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-16-2013, 02:05 AM
  5. [SOLVED] Vlookup – Picking up first date and then picking up second date if there is one.
    By CranstonWatts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2012, 11:32 AM
  6. Replies: 5
    Last Post: 06-11-2006, 04:45 PM
  7. Replies: 2
    Last Post: 03-08-2006, 04:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1