+ Reply to Thread
Results 1 to 7 of 7

Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Function

  1. #1
    Registered User
    Join Date
    03-17-2022
    Location
    NJ USA
    MS-Off Ver
    Office 365
    Posts
    3

    Smile Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Function

    Hi,

    I need to populate two columns based on a reference sheet like below.

    For example the below is reference -

    Position Ability Speed


    1 High High

    2 Medium High

    3 Low High

    4 Low Medium

    5 Low Low

    And now on another sheet of excel using the above reference I should be able to 'default' automatically using a Formula or Function the Ability & Speed values when there is Position value entered by User.

    Example when - Position Ability Speed

    1 High High

    Request to share your suggestions. Thanks

    Vk

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

    Hello vk.excel. Welcome to the forum.

    We really need an Excel sample workbook. Please see instructions in the 'gold' banner at the top of the page. HOW TO ATTACH YOUR SAMPLE WORKBOOK:
    Dave

  3. #3
    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,999

    Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

    One way

    =LOOKUP($F$2,{1,2,3,4,5},{"Low","Low","Low","Medium","High"})

    =LOOKUP($F$2,{1,2,3,4,5},{"High","High","High","Medium","Low"})

    OR set up table with data (say) columns A:C as per your posted file

    then

    =VLOOKUP(Position,$A$1:$C$5,2,0)

    and

    =VLOOKUP(Position,$A$1:$C$5,3,0)
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Registered User
    Join Date
    03-17-2022
    Location
    NJ USA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

    Hi,

    Thanks for the responses. Vlook up can be applied but still am looking for any logic that would populate upon entering 'Position' value.

    Any other solution please?

    Thanks,

    Vk

  5. #5
    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,999

    Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

    With the formula is the desired cells they will populate when position is entered

    =VLOOKUP(P1,$A$1:$C$5,2,0) position in P1

    this in Q1

    =VLOOKUP(P1,$A$1:$C$5,2,0) will return value in B

    this in R1

    =VLOOKUP(P1,$A$1:$C$5,3,0) will return value in C



    so not sure what you want

  6. #6
    Registered User
    Join Date
    03-17-2022
    Location
    NJ USA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

    Hi John,

    Appreciate for your response.

    I did vlook up and it is fetching result only for first few rows and later on it shows error - N/A - A Value is not available to the Formula or Function.

    Though there are values for the Position in the reference, not sure why it is so?

    For example - there is Ability value 'Low' for Position - 3, 4 & 5. Is it due to that and I dont think so.

    Thanks

  7. #7
    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,999

    Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

    Try

    for first cell

    =If ($F2="","",LOOKUP($F2,{1,2,3,4,5},{,"High","Medium","Low","Low","Low"}))

    my error!

    and

    =IF($f2="","",LOOKUP($F2,{1,2,3,4,5},{"High","High","High","Medium","Low"}))

+ 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 function formula Returning value from different sheet based on text in Cell
    By kenny scott in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-08-2020, 11:39 AM
  2. Replies: 5
    Last Post: 08-03-2020, 04:15 PM
  3. Replies: 1
    Last Post: 03-13-2018, 09:16 AM
  4. VBA - Search Sheet 1 for text, reference corresponding cell, populate Sheet 2?
    By rdr06001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2016, 01:30 PM
  5. [SOLVED] How to populate cells with data from another sheet based on an X in certain columns
    By WWasmer2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2015, 10:44 AM
  6. Indirect Sheet reference with cell reference left function and text
    By teststrip in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2015, 10:31 AM
  7. Replies: 12
    Last Post: 05-11-2012, 03:09 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