+ Reply to Thread
Results 1 to 6 of 6

Almost There! Match values in two columns and return text in adjacent cell

  1. #1
    Registered User
    Join Date
    08-12-2012
    Location
    United States
    MS-Off Ver
    MS 365
    Posts
    16

    Almost There! Match values in two columns and return text in adjacent cell

    I have a report that I put together each week with performance measures for a number of locations. The report is pulled down from a third party website and is in Excel format but not the format that I need. In the attached example; column A, B & C are how the data comes in. (There are 5 more performance measures that would appear in adjacent rows but for simplicity sake, I have only included two) Columns F&G and I&J represent how I need it to look. G & J use the LARGE formula to sort the data highest to lowest and I think that formula is okay. Columns F & I need to have the location name from column A that correlates to the ranking number. I have managed to use VLOOKUP to return the number but not that name. As the title suggests, I think I'm close but not quite there. Suggestions for any formulas/approaches are welcome. - Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,556

    Re: Almost There! Match values in two columns and return text in adjacent cell

    For F2 and down you could use the formula:
    Please Login or Register  to view this content.
    For I2 and down you could use the formula:
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-12-2012
    Location
    United States
    MS-Off Ver
    MS 365
    Posts
    16

    Re: Almost There! Match values in two columns and return text in adjacent cell

    Perfect! Just what I needed. Thanks JeteMc!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,556

    Re: Almost There! Match values in two columns and return text in adjacent cell

    You're Welcome and thank you for the feedback. If you haven't already please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

  5. #5
    Registered User
    Join Date
    01-10-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Almost There! Match values in two columns and return text in adjacent cell

    There are duplicate values in the cols G & J, so I believe a De-Duplicator column needs to be added.
    In D2 write =B2 + COUNTIF($B$1:B1,$B2)*0.0005 and copy it down
    In G2 change formula to =INDEX($B$2:$B$22,MATCH(LARGE($D$2:$D$22,ROWS($E$2:E2)),$D$2:$D$22,0)) & copy down
    In F2 change formula to =INDEX($A$2:$A$22,MATCH(LARGE($D$2:$D$22,ROWS($E$2:E2)),$D$2:$D$22,0)) & copy down.

    You'll need to do the same for GSI Rank since 79.7 & 66.7 occur more than once.

  6. #6
    Registered User
    Join Date
    08-12-2012
    Location
    United States
    MS-Off Ver
    MS 365
    Posts
    16

    Re: Almost There! Match values in two columns and return text in adjacent cell

    Thanks canoeron. I didn't notice that and it would be a problem if any of the top three in a given category were identical values. Good catch. Thanks!

+ 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. Look up values in 2 adjacent columns. Return values in 3 columns if match.
    By Gssoc777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-04-2015, 12:28 PM
  2. [SOLVED] Function to match text and return number in adjacent cell?
    By danoswalt in forum Excel General
    Replies: 6
    Last Post: 12-16-2014, 01:47 PM
  3. Search for a text string and return adjacent cell value for each match
    By ral8088 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2013, 09:55 AM
  4. Find and Match Text in Column Return Adjacent Cell
    By biancam in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-11-2012, 02:45 PM
  5. [SOLVED] Match text in 2 columns, if true output adjacent cell to the match.
    By kenoboy in forum Excel General
    Replies: 3
    Last Post: 07-03-2012, 09:52 AM
  6. Traverse a colum match a text and return sum of all values in adjacent cells
    By realdost in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2012, 09:49 PM
  7. Compare values in two columns and return text from adjacent cell
    By MrBorders in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-04-2009, 04:54 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