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

1. ## 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

2. ## 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

3. ## 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

4. ## 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