Results 1 to 8 of 8

formula to find a number in a range then use the data in same column but row 16

Threaded View

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    648

    formula to find a number in a range then use the data in same column but row 16

    I have a sheet that in a cell I want to search a range (O19:W27) and if it finds an exact match to the data in (F7) EX:"SDM-0011" ---data will have numbers and letters and will change. If it finds that exact match in the specified range it grabs the number in the same column above it on row 16.

    Sometimes the same match will be found in the specified range more than once so I would like the number grouped together separated by comma.
    Ex: if found in 2 spots and the number in row 16 are 2 & 3 then it would return "2,3" in cell (E11)

    I use this formula on other parts of the sheet

    =IF(F7="","",SUMPRODUCT(($O$19:$W$27=F7)*IF(ISNUMBER($O$7:$W$7),$O$7:$W$7)))

    an array that adds the numbers together that match what it was looking for but instead of adding the data together I want it to display it as it is.

    Right now it returns "5"
    what I want is "2,3"

    I know its doing it because of the "sumproduct" but can I use something else to give me the result I want
    Last edited by bdouglas1011; 01-16-2019 at 03:39 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find number in range and return the value in another column
    By HangMan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-10-2017, 01:36 PM
  2. [SOLVED] Find column number from specific range
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-30-2016, 09:12 AM
  3. Find row & column number of cells in a range with lookup functions
    By ieumts in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-02-2014, 03:01 AM
  4. Find column number or range in a row (multiple ranges)
    By Martijn79 in forum Excel General
    Replies: 15
    Last Post: 04-13-2014, 06:29 AM
  5. [SOLVED] Find and Get Column Letter and Row Number from Duplicates in Range of Cells
    By WITJ in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2013, 12:23 PM
  6. How to find a cell from a range and identify its column number
    By JamesGoulding85 in forum Excel General
    Replies: 2
    Last Post: 06-06-2013, 08:29 AM
  7. [SOLVED] find bold number in Column A and copy range to column C
    By joek13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-24-2012, 10:32 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