+ Reply to Thread
Results 1 to 6 of 6

Formula to always return cell below what a vlookup would return

  1. #1
    Registered User
    Join Date
    08-27-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    2

    Formula to always return cell below what a vlookup would return

    I'm working with spreadsheets where data and values are all across one row, and below the values in the next row are the corresponding %'s of budget achieved (usually these are just in the next column - easy!). As these spreadsheets aren't mine I don't want to manipulate any data, was just hoping there would be a formula that could return the cell below one that I would do a normal vlookup for (to give me the % rather than the $ value). Is this possible? Thanks for any help you can provide.

    EG: How to return the cell below =VLookup("John",A1:C4,3,0)

    A B C
    1 JOHN 0.5 $50,000
    2 SYDNEY 88%
    3 PETER 1.0 $120,000
    4 MELBOURNE 94%

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Formula to always return cell below what a vlookup would return

    Please attach a sample workbook if possible
    Cheers!
    Deep Dave

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,281

    Re: Formula to always return cell below what a vlookup would return

    You should have posted a workbook. I can't tell whats in each column. If you do MATCH("John",B:B,0) it will return the row number, so, combining with INDEX you can use something like:
    INDEX(C:C, MATCH("John",B:B,0)+1)
    Ben Van Johnson

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Formula to always return cell below what a vlookup would return

    Please see the file attached and let me know if this is what you require.

    Deep
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-27-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Formula to always return cell below what a vlookup would return

    Thank you, sorry I didn't realise about attaching workbooks but you got it spot on anyway and it worked. Thanks!

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Formula to always return cell below what a vlookup would return

    Glad it helps..

    Deep

+ 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. [SOLVED] IF statement to return 0 if 0 in cell if not return formula
    By CraigieL in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-15-2013, 09:46 AM
  2. Vlookup advanced - might be a different formula needed (Return cell value)
    By tdearling in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-12-2013, 01:49 PM
  3. [SOLVED] Need formula to return blank instead of #N/A when source cell is blank, using VLOOKUP
    By TMB1234 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2013, 07:16 PM
  4. Vlookup return error if cell contain formula
    By celticpucca in forum Excel General
    Replies: 1
    Last Post: 05-22-2011, 08:40 AM
  5. Vlookup Formula does not return value
    By dcraker in forum Excel General
    Replies: 8
    Last Post: 04-14-2008, 01:42 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