+ Reply to Thread
Results 1 to 9 of 9

Formula/Function to lookup and display value

  1. #1
    Registered User
    Join Date
    01-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    Windows 7
    Posts
    36

    Formula/Function to lookup and display value

    I am trying to figure out to display a certain value on sheet 1. The following table is on sheet 2. Cell A 1 will be populated with a value of 1-5. For example, if a 1 is in cell A 1, I need to return the value of 03, representing a Toyota. If a 2 is in A 1, I need to return the value of 05, representing a BMW. If a 3 is in A 1, I need to return a value of 04, representing Nissan. If a 4 is in A 1, I need to return a value of 01, representing Ford. And finally, if a 5 is in A 1, I need to return the value of 02, representing Chevy. Thank you.



    A B C
    1 1 Ford 01
    2 Chevy 02
    3 Toyota 03
    4 Nissan 04
    5 BMW 05
    6
    7

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula/Function to lookup and display value

    I'm struggling to understand this. Your descriptions, e.g "if a 5 is in A 1, I need to return the value of 02, representing Chevy." don't seem to relate to the table you show. For instance what's the c onnection between 5 in column A and 02 in C2. Or are you saying that your text description is a definitive mapping in its own rignt?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    Windows 7
    Posts
    36

    Re: Formula/Function to lookup and display value

    Sorry, the table I pasted in the post clearly formatted poorly. I don't see how to attach a sample either.

    Column A, only cell 1 will have a value at any given time, that will change - between 1 and 5. Column B will have the car makes, Column C will have 1 through 5 descending. So depending on what value is in A1, the corresponding value will return based on my initial post. Does that help?
    Attached Images Attached Images

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula/Function to lookup and display value

    Sorry but I still don't see how a 3 in A1 for instance results in '04'.
    How are those connected? What's the rule?

    I'm also puzzled by your reference to column C having 1 to 5 descending. Your example has them ascending although whether this is relevant to my question I have no idea.

  5. #5
    Registered User
    Join Date
    01-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    Windows 7
    Posts
    36

    Re: Formula/Function to lookup and display value

    Disregard the descending comment.

    The predefined correlation is if a 1 is in cell A 1, I need to return the value of 03, representing a Toyota. If a 2 is in A 1, I need to return the value of 05, representing a BMW. If a 3 is in A 1, I need to return a value of 04, representing Nissan. If a 4 is in A 1, I need to return a value of 01, representing Ford. And finally, if a 5 is in A 1, I need to return the value of 02, representing Chevy.

    So, cell A 1(on sheet 2) will change. If a 3 is in that A1, I need to display an 04 on sheet 1, representing Nissan. The cell on sheet 1 I need the result displayed is C 5. Make sense? Would this just be if/then type of logic? I am just not accustomed to doing this type of formula in Excel.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: Formula/Function to lookup and display value

    It really would help if you posted a small file showing expected outcomes.

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula/Function to lookup and display value

    Quote Originally Posted by JohnTopley View Post
    It really would help if you posted a small file showing expected outcomes.
    ... and indeed the precise rule

    @pingman3
    Are you saying that the rule is always
    If A1 = 1 then return the value 3
    If A1 = 2 then return the value 5
    If A1 = 3 then return the value 4
    If A1 = 4 then return the value 1
    If A1 = 5 then return the value 2

    that's ok for the five items you specifically show but the implication in your first post is that A1 might have other values like 6 or 7 ...and are there others.
    If the mapping above is all you need then just create a Vlookup table with the values 1-5 in the first column and the values 3,5,4,1,2 in the second column. Then the VLOOKUP would be assuming the table is in say C1:D5

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    Windows 7
    Posts
    36

    Re: Formula/Function to lookup and display value

    You are correct, the rule would be static for those five items. That original post was confusing due to formatting of what I was trying to display.

    This VLOOKUP seems to work, but here is what I am running into. So my A1 cell on sheet two I have pulling in from a cell on sheet 1. Sheet 1 in an XML where I am importing an XML file into an XML map. For some reason, when A1 on sheet two picks up the value imported in on sheet 1, the VLOOKUP displays #N/A. If I manually type a 1-5 in cell A1 - VLookup works like a champ. I thought it was a formatting thing at first, as the XML cell on sheet 1 appeared to be 'text'. I changed that to general, and number, still didn't fix the problem. I can seem to figure out why Vlookup doesn't work when I have A1 = to a cell on sheet 1.

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula/Function to lookup and display value

    Hi,

    Undoubtedly it will be because the cell referred to on sheet1 by the A1 cell on sheet2 is not numeric. Test it by entering the formula =ISNUMBER(A1) in a cell somewhere on the sheet1. I'll bet a pound to a penny that you get the answer FALSE meaning A1 might show the character '1 it's actually a string character not a numeric. Either change the VLOOKUP table to hold text values in the first column or change the formula I gave you to

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. Function/Formula to display Stones/Pounds/Ounces
    By dkk168 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-12-2015, 04:57 AM
  2. [SOLVED] Display data with lookup formula
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-05-2014, 06:57 PM
  3. Replies: 6
    Last Post: 05-06-2013, 04:14 PM
  4. Create a lookup formula with an If or Lookup function
    By afountas21 in forum Excel General
    Replies: 2
    Last Post: 09-05-2012, 06:53 PM
  5. [SOLVED] Function/Formula to display minimal sell price for profit
    By DKSJ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-22-2012, 10:17 AM
  6. Display formula from insert function
    By natejensen88 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-22-2011, 11:37 AM
  7. [SOLVED] Display cell formula by function
    By 0-0 Wai Wai ^-^ in forum Excel General
    Replies: 15
    Last Post: 09-09-2005, 01:05 AM

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