Results 1 to 9 of 9

Matrix Lookup Issue with ISNUMBER & SEARCH

Threaded View

  1. #1
    Registered User
    Join Date
    05-26-2022
    Location
    Devon, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    3

    Matrix Lookup Issue with ISNUMBER & SEARCH

    Hi,

    I work in a laboratory, and we have various freezers named: Fz1, Fz2, ..... Fz9, Fz10, Fz11, Fz12

    We keep various samples in these freezers such as blood.

    I have created a large spreadsheet which links each sample type to the freezers which store them. (The table also has more columns with more information I have kept out)
    See below a very basic example

    Saliva Fr2; Fr6
    Urine Fr1
    Plasma Fr1; Fr12
    Serum Fr8; Fr11; Fr12

    I have created another tab with a table of sample types going down the page and freezers going across.
    I want the cells to link back to the tab described above, highlighting the cells which show which freezers contain which samples, as shown in the image.
    The cells have a formula =ISNUMBER(SEARCH(B$1,Freezer!$A$1)) where Freezer is the other tab containing the table
    Freezer example.PNG


    However the issue is this says Freezer Fr1 contains Serum which isn't the case.
    I understand this is because Fr11 and Fr12 contain "Fr1" in them.
    I have tried ways of editing this, however, any solution I have found doesn't work as there are other freezer names in the original cell it's pulling from. I also want to keep the Substring as the cell rather than the text, as we have many freezers.
    Attached Images Attached Images
    Last edited by AliGW; 05-26-2022 at 07:26 AM. Reason: Title changed - please think more carefully about your thread titles in future!

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using the IF(ISNUMBER(SEARCH) function
    By smbyrnecarey in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-20-2022, 07:22 AM
  2. [SOLVED] IF(AND(ISNUMBER(SEARCH())) Function
    By joshuarobbins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-30-2015, 12:57 PM
  3. [SOLVED] Countifs & Search (ISNumber) Function together
    By MeritaH in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2014, 11:01 PM
  4. Combine IF THEN Function with ISNUMBER SEARCH?
    By alexa_raye in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2014, 11:44 AM
  5. [SOLVED] IF(ISNUMBER(SEARCH function
    By NickT83 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-16-2014, 10:18 AM
  6. ISNUMBER(SEARCH Function
    By somesoldiers in forum Excel General
    Replies: 2
    Last Post: 07-14-2009, 10:49 AM
  7. SUMPRODUCT((ISNUMBER(SEARCH() function
    By redneck joe in forum Excel General
    Replies: 13
    Last Post: 12-08-2006, 06:19 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1