+ Reply to Thread
Results 1 to 4 of 4

Match formula to search range of columns to return True or False result

  1. #1
    Registered User
    Join Date
    09-07-2013
    Location
    Chester
    MS-Off Ver
    Home: 2011 for Mac Work: 2007
    Posts
    70

    Match formula to search range of columns to return True or False result

    Hi

    Hoping somebody can help with a slight problem I'm encountering

    I have a sheet of approximately 850 employees that I need to cross check against a data file supplied by an external stakeholder, I have been using a simple MATCH formula to check whether the colleagues' employment number and log in ID appears on the external data file, which works well on the whole however the raw data file supplied by the external company is a mess in that the required information isn't consistently in the same column. Most employment numbers appear on column D for example however the data may also appear in other columns, the same applies to the log in ID.

    I only need to return a True or False result on whether the employee number and log in ID are on the raw data file rather than lookup any additional information

    I've tried using an IF(COUNTIF formula to check a range of columns which works, however the raw data file contains c9000 rows, with data spread inconsistently across 6 or 7 columns, and I need to run the check on 850 odd colleagues so as you can imagine the formula is pretty labour intensive.

    Does anyone have any thoughts on how I could complete the check however with a more manageable formula

    Hope that all makes sense

    Thanks in advance for any thoughts and suggestions

    PS I'm not able to request the external stakeholder reformat their data as it's an extract directly from their system

    Twaddy

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,964

    Re: Match formula to search range of columns to return True or False result

    You could try creating a composite column with a formula like

    = "|" & A1 & "|" & B1 & "|" & C1 & "|"

    Then look for the ID as a substring

    = COUNTIF(Z1,"*|" & AD1 & "|*")

    where the first formula is in Z1 and your ID is in AD1
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Registered User
    Join Date
    09-07-2013
    Location
    Chester
    MS-Off Ver
    Home: 2011 for Mac Work: 2007
    Posts
    70

    Re: Match formula to search range of columns to return True or False result

    Hi mrice

    Thanks very much for your reply and suggested formula, not one I've used before with a substring but it certainly works

    I'll need to test it tomorrow at work with large amounts of data however it should hopefully be a lot less processor heavy than doing the COUNTIF across multiple columns.

    I've also tweaked it slightly to an IF(COUNTIF formula to return True or False but it would work just as well with ones and zero's

    Quick question, should your COUNTIF formula return 2 if the ID appears twice in the initial formula results that you then search for the ID as a substring? Or will it only ever return 1?

    Thanks again

    Twaddy

  4. #4
    Registered User
    Join Date
    09-07-2013
    Location
    Chester
    MS-Off Ver
    Home: 2011 for Mac Work: 2007
    Posts
    70

    Re: Match formula to search range of columns to return True or False result

    Hello again

    I've tweaked it further to carry out a second check to see if the employee number and log on ID are present on the same row which they should be

    Probably worth a check to see if the employee number is present as a standalone piece of data, and do the same for the ID, and then the joined up check to ensure they're on the same row

    This is how I've done it, not sure if you'd do it any differently (The cell references are a bit random, just how it came out on a test sheet)

    =IF(COUNTIFS(H6:H7,"*I"&D11&"I*",H6:H7,"*I"&C11&"I*")>0,"TRUE","FALSE")

    Cheers

    Twaddy

+ 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. Function to search Column for a range of values and return TRUE or FALSE
    By BamBamMoneyBags in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-05-2013, 05:59 AM
  2. Search Range and return True or False
    By Grimace in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2010, 06:41 PM
  3. Replies: 3
    Last Post: 07-15-2006, 05:20 AM
  4. Search column for value and return TRUE or FALSE
    By Remote Desktop Connection hotkey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. Search column for value and return TRUE or FALSE
    By Remote Desktop Connection hotkey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 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