+ Reply to Thread
Results 1 to 4 of 4

Find using Unique Identifier & Value, and then Return a Different Cell Value

  1. 06-01-2015, 12:48 PM #1
    _HRIS_
    _HRIS_ is offline
    Registered User
    Join Date
    04-17-2015
    Location
    Orlando, FL
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    2

    Find using Unique Identifier & Value, and then Return a Different Cell Value

    Hello - First post here!

    I need help with a formula please. Example attached.

    I have a Unique Identifier in Spreadsheet.1, and I'm trying to find a Metric in Spreadsheet.2 that begins with a certain value, that is associated with that Unique Identifier. If the value is found, then I would like the formula to return the Metric ID in spreadsheet.2 (that is associated with that Metric and that Unique Identifier), into spreadsheet.1. If the value is not found, then it should just return "Not Found". Metrics Example_1_HRIS_6.1.2015.xlsx

    I need to do this for (6) different metrics:
    "Q1 -"
    "Year to Date"
    "Pipeline"
    "Marketing"
    "Sales"
    "DSM"

    The purpose is to update existing metrics with current data, based on the Metric ID. I need the correct Metric ID, for that Unique Identifier and Metric, in order to update our system.

    Please see attachment for example. Let me know if any additional info is needed. Thanks for taking the time to review!
    Reply With QuoteRegister To Reply
  2. 06-01-2015, 12:54 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    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: Find using Unique Identifier & Value, and then Return a Different Cell Value

    Hi, and welcome to the forum

    See attached.
    Attached Files Attached Files
    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.
    Reply With QuoteRegister To Reply
  3. 06-01-2015, 12:56 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,071

    Re: Find using Unique Identifier & Value, and then Return a Different Cell Value

    Hi there... and welcome to the Excel Forum. Try this in B4, copied down...

    =IFERROR(INDEX($G$4:$G$33,MATCH(1,INDEX(($F$4:$F$33=A4)*($H$4:$H$33=C4),0),0)),"Not Found")
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 06-01-2015 at 12:58 PM. Reason: Added attachment
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 06-01-2015, 01:16 PM #4
    _HRIS_
    _HRIS_ is offline
    Registered User
    Join Date
    04-17-2015
    Location
    Orlando, FL
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    2

    Re: Find using Unique Identifier & Value, and then Return a Different Cell Value

    Quote Originally Posted by Glenn Kennedy View Post
    Hi there... and welcome to the Excel Forum. Try this in B4, copied down...

    =IFERROR(INDEX($G$4:$G$33,MATCH(1,INDEX(($F$4:$F$33=A4)*($H$4:$H$33=C4),0),0)),"Not Found")

    Thank you both for the quick replies! These formulas do work great, however I'm not trying to find an exact match between the Old and New Metrics (column H & C) - I'm trying to find the beginning of the Metric name (for example: "Q1 -" or "Year to Date"), and then return the Metric ID based on that and the Unique Identifier.. Apologies if I did not explain clearly!

    In Glenn's formula for instance where it says ($H$4:$H$33=C7), I would need it to somehow say "in column H4:H33, starts with "Q1 -"".

    See addtl example. Thanks

    Metrics Example_2_HRIS_6.1.2015.xlsx
    Last edited by _HRIS_; 06-01-2015 at 01:55 PM. Reason: Added quote
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to find unique identifier and add data to cell
    By browndog in forum Excel General
    Replies: 1
    Last Post: 10-19-2014, 09:23 PM
  2. Return values from multiple rows (with unique identifier) into single row - Arrays/Index?
    By nickjcam in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2013, 09:45 PM
  3. Macro to move data from one cell to another where unique identifier changes
    By boothyuk123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2012, 05:36 PM
  4. Combine rows of data into 1 cell based on unique identifier
    By jud_goh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-11-2010, 05:06 PM
  5. Formula to return a unique identifier to a row of data based on 2 columns?
    By krups in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2009, 07:02 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1