Lookup reference formula

[jrabi]

I have a file with 4 sheets.

Each sheet has 2 columns, column A is the source code, column b is the source name.

I want to place a formula in sheet 1 column C to see if any of the same source codes are on sheets 2,3 or 4.

So I want to look at the source code on sheet 1 column A and see if this source code is on any of the 3 sheets, if it is, then I would like to return a value of "match" or "no match"

Any help would be appreciated, thanks.

 

[Jon von der Heyden]

In C2 try;
=IF(SUM(COUNTIF(INDIRECT({"Sheet2";"Sheet3";"Sheet4"}&"!A:A"),A2)),"match","no match")

Change sheet names to suit....

 

[jrabi]

I am getting a #REF!

=IF(SUM(COUNTIF(INDIRECT({"compare 1";"compare 2";"compare 3"}&"!A:A"),A2)),"match","no match")

I have excel 2007.

 

[pgc01]

Hi

If a worksheet name has spaces you have to enclose it in single quotes.

Try:

=IF(SUM(COUNTIF(INDIRECT("'"&{"compare 1";"compare 2";"compare 3"}&"'!A:A"),A2)),"match","no match")

 

[T. Valko]

I am getting a #REF!

=IF(SUM(COUNTIF(INDIRECT({"compare 1";"compare 2";"compare 3"}&"!A:A"),A2)),"match","no match")

I have excel 2007.

Try it like this...

=IF(SUM(COUNTIF(INDIRECT("'Compare "&{1,2,3}&"'!A:A"),A2)),"Match","No Match")

Note that the sheets Compare 1, Compare 2 and Compare 3 must exist or you'll get an error.

 

[jrabi]

That was it, I was just getting ready to change the name back to sheet1, sheet2, another good tip to know so I am glad I got your response, thanks to all.

 

[jrabi]

Thanks T. yours works fine too.

 

[T. Valko]

Thanks T. yours works fine too.

You're welcome. Thanks for the feedback!

 

[jrabi]

Ok, I have a slight mod, what if on the sheet with the formula I insert a column at C so the formula resides in D. A is the source code, B is the source name.

I want to add to the formula if in Column C it is a 4, then check only the sheets that are "compare 1", "compare 2", "compare 3" and "4".

In column C for the 4 which means its a 4 door, I would enter a 4 for every source code. Or it may be a 5, 6, etc.

Is there a way to add to the formula look at column c and if it matches any other sheet check for a match?