+ Reply to Thread
Results 1 to 5 of 5

Excel Reference Formula Help

  1. #1
    Registered User
    Join Date
    02-25-2015
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    7

    Excel Reference Formula Help

    Here's an example of what I'm working on
    A1 B1 D1 E1 F1
    Year Car Model Used New Mileage
    2000 Toyota Highlander 15 27 20,500
    2001 Kia Optima 8 16 15,400
    2002 Dodge Charger 18 31 0
    etc



    im looking for a formula for D1,E1,F1,etc, so that when i type the specific year and car model in A1 and B1 , it brings back the row of data that i have on another worksheet. the data changes for each model by year, and i have 16 different tables of data (one table for each year 2000-2015). i have been manually typing in the data for each year but i figured their was an easier way. I was thinking it was a reference formula question. any and all help is much appreciated. thanks in advance.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,969

    Re: Excel Reference Formula Help

    Hi, welcome to the forum

    Sounds like you need to look at using INDEX/MATCH for this?
    i have 16 different tables of data (one table for each year 2000-2015)
    Why? Why not 1 table with all data, it will probably make searches a LOT easier

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-25-2015
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Excel Reference Formula Help

    sample book.xls

    thanks FDIB...one table shouldn't be a problem i just have all the data sorted by year right now..i'm uploading a sample workbook that hopefully explains my question.
    Last edited by rsc87; 02-25-2015 at 01:23 PM. Reason: grammar

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,969

    Re: Excel Reference Formula Help

    THis should give you what you want...
    =IFERROR(INDEX(Sheet2!E$3:E$21,MATCH(Sheet1!$B3&Sheet1!$C3,INDEX(Sheet2!$C$3:$C$21&Sheet2!$D$3:$D$21,0),0)),"")

    Only the 1st row in your sample data has any matches

  5. #5
    Registered User
    Join Date
    02-25-2015
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Excel Reference Formula Help

    Hey FDIB, i was reading up on INDEX MATCH while waiting for responses, and i think that's what i need, however i'm having trouble understanding the formula you suggested..all im getting is an error message when i use it.

+ 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] Excel Cross-Reference Formula?
    By meggles in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-18-2013, 12:26 PM
  2. [SOLVED] Excel table - using a formula in a structured reference
    By jankee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-22-2013, 08:10 AM
  3. [SOLVED] & in file reference in excel formula
    By Alexander_Golinsky in forum Excel General
    Replies: 5
    Last Post: 07-24-2012, 01:05 AM
  4. [SOLVED] Issues with my excel formula as it needs Circular reference - hlp
    By im_not_excelling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-04-2006, 03:25 AM
  5. Excel Formula Reference?
    By NickDangr in forum Excel General
    Replies: 4
    Last Post: 02-25-2005, 01:06 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