+ Reply to Thread
Results 1 to 4 of 4

Return values in columns

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    Tallahassee, FL
    MS-Off Ver
    Excel:Mac 2011
    Posts
    5

    Return values in columns

    Hello All,

    I am attempting to create an auto-populated form on the 'JRN Form' sheet. In cell D4 I can select a receipt number which represents a transfer of funds. These numbers correspond to the numbers in column A of 'Transfer Basic' sheet. Once I select the receipt number I would like the form to auto-populate with the correct information. The two areas that I can't figure out are the Receipt # column(B11:B18) and the Amount Transferred column (G11:G18). The 'Amount Transferred' column should return the amounts in order in the row of the 'Transfer Basic' sheet that corresponds to the value in cell D4 of the 'JRN Form' sheet. The 'Receipt #' column should return a value in row 1 of the 'Transfer Basic' sheet that corresponds to the amount returned in the 'Receipt #' column. For example, if I enter 15-046 into cell D4, B11 should return 14-034, G11 should return $203.31, B12 should return 14-035, and G12 should return $356.19.

    I have tried various arrangements of INDEX and MATCH, VLOOKUP, etc. and can't get the right solution. Let me know if you need any more information.
    Thanks in advance!

    Ledger14-15test.xlsm

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Return values in columns

    Try this Array Formula. Since this is an Array Formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter.
    In B11
    Please Login or Register  to view this content.
    and copy down.

    In G11
    Please Login or Register  to view this content.
    and copy down.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    08-12-2013
    Location
    Tallahassee, FL
    MS-Off Ver
    Excel:Mac 2011
    Posts
    5

    Re: Return values in columns

    That is genius! Works like a charm! Thank you so much!

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Return values in columns

    You're welcome. Glad I could help.
    You may also click on * (star) to Add Reputation if the solution provided helped you. This is another way to say thanks.

+ 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. Compare values in two columns and return values from a third
    By Ender1771 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-24-2013, 11:08 AM
  2. How to find closest values in multiple columns and return adjacent values.
    By Patrician in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 01:47 PM
  3. matching values based on criteria and return values from another columns
    By lizard54 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 11:29 AM
  4. Match Columns in 2 Worksheets, return only changed values of other columns
    By cheynooki in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-17-2011, 01:19 PM
  5. Look across values in 12 columns, return the number of unique values
    By NukedWhale in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-21-2009, 05:18 PM

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