Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Find value in a range and return Y/N if adjacent cell contains specific text

1. I agreed to these rules
Join Date
9th August 2012
Posts
2

## Find value in a range and return Y/N if adjacent cell contains specific text

Hi all,

I am having a lot of difficulty with this.

I am looking for a formula to do the following:

First I want it to check whether the text in my cell Sheet1 A1 is present in a range in Sheet3 i.e. A1:A250 (which I know it may be in multiple instances).
If the text value of A1 Sheet1 is present, I want an adjacent cell in the row to be checked for a specific text e.g. "352FR1".
If text value "352FR1" is present in an adjacent cell to any row containing my text value from A1 Sheet1 then I want the value Yes returned in my formulated cell!!

Very simple!!

I can provide further info as required!

cheers,

Excel Video Tutorials / Excel Dashboards Reports

2. Member
Join Date
11th June 2011
Location
Amsterdam
Posts
58

## Re: Find value in a range and return Y/N if adjacent cell contains specific text

If you want to avoid using VBA a solution might be to create another column in Sheet3 ("C") which concatenates column A and B. Now you only have to check for a single text, namely the text in range A1 and "352FR1". If you do not want to add another column, you can also use an array formula:
VB:
=Not(ISERROR(MATCH(CONCATENATE(A1;"352FR1");CONCATENATE(Sheet3!A1:A250;Sheet3!B1:B250;0))))

As with any array formula, use Shft+Cntrl+Enter to enter it.

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
9th August 2012
Posts
2

## Re: Find value in a range and return Y/N if adjacent cell contains specific text

HI,

Thanks for that. I think this will work perfectly but I am having some trouble with the formula.

I am returning an error when entering it. Excel does not seem to want to accecpt (A1;"352FR1") it is returning an error on the A1 part.

Any ideas?

Excel Video Tutorials / Excel Dashboards Reports

4. Member
Join Date
11th June 2011
Location
Amsterdam
Posts
58

## Re: Find value in a range and return Y/N if adjacent cell contains specific text

Maybe you need to use "," instead of ";" as a seperator? Otherwise I do not have a clue for now.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Find value in a range and return Y/N if adjacent cell contains specific text

Try:

=IF(ISNUMBER(MATCH(A1,Sheet3!\$A\$1:\$A\$250,0)),IF(VLOOKUP(A1,Sheet3!\$A\$1:\$B\$250,2,0)="352FR1","YES",""),"")

6. Member
Join Date
11th June 2011
Location
Amsterdam
Posts
58

## Re: Find value in a range and return Y/N if adjacent cell contains specific text

Doesn't that only check whether the value "352FR1" is next to the first occurence of A1? It could of course be that "352FR1" is next to a later occurence.

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Find value in a range and return Y/N if adjacent cell contains specific text

True, then perhaps:

=IF(ISNUMBER(MATCH(A1,Sheet3!\$A\$1:\$A\$250,0)),IF(COUNTIFS(Sheet3!\$A\$1:\$A\$250,A1,Sheet3!\$A\$1:\$A\$250,"352FR1")>0,"YES",""),"")

or if you have pre XL2007...

=IF(ISNUMBER(MATCH(A1,Sheet3!\$A\$1:\$A\$250,0)),IF(SUMPRODUCT(--(Sheet3!\$A\$1:\$A\$250=A1),--(Sheet3!\$A\$1:\$A\$250="352FR1"))>0,"YES",""),"")

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno