Matching Data / Lookup from different sheets

[Elegidos]

Hi everyone,

I have a question about how to match up data from one sheet to another. I've attached an example of what I want to do.



Example:

Sheet1 has 15 rows and 17 columns of information (numbers) in each cell.

Sheet2 has 1 column of numbers of 75 cells of information (numbers).

What I want to know if it's possible is that every cell in sheet2 be compared with sheet1 and if a cell from sheet2 matches with a cell in sheet1 you can change the font color or background color or similar? Also the information from sheet2 will change often.


Thanks in advance,
Elegidos (Spain)

[NBVC]

First name the range in Sheet1...

Select the range and in the top left area of the sheet, next to the formula bar, in the Name Box, enter the word RANGE.

Then in Sheet2, select all of column D and go to Format|Conditional Formatting.. then select Formula Is and enter formula:

=COUNTIF(RANGE,D1)

Click Format and choose from the Pattern Tab

[Elegidos]

First name the range in Sheet1...

Select the range and in the top left area of the sheet, next to the formula bar, in the Name Box, enter the word RANGE.

Then in Sheet2, select all of column D and go to Format|Conditional Formatting.. then select Formula Is and enter formula:

=COUNTIF(RANGE,D1)

Click Format and choose from the Pattern Tab

Perfect, thank you very much. The only problem is that I wanted it backwards but I figured it out since it's doing it the other way around. Thanks


First name the range in Sheet2...

Select the range and in the top left area of the sheet, next to the formula bar, in the Name Box, enter the word RANGE.

Then in Sheet1, select all the cells with the numbers and go to Format|Conditional Formatting.. then select Formula Is and enter formula:

=COUNTIF(RANGE,A1)

Click Format and choose from the Pattern Tab

[Elegidos]

P.D.

Forgot to attach the example with the solution. Just incase it helps more people if they have the same question.

[NBVC]

Thanks for the feedback

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[Elegidos]

Thank you for the help .

Before I change it to solved. One quick question. Using the same example in the first post. Instead of looking up the informatcion from sheet2, can it be done thru a text file?

[NBVC]

You mean look for words in a Word Document or similar?

I don't think so, not without some VBA possibly....

If that is what you really want, then post in the programming forum..with descriptive title.

[Elegidos]

Thank you NBVC. I will do so then .

Un Saludo,
Elegidos (Spain)

[Elegidos]

Another question related with the first post above. If instead of having just a number in each cell I have for example: "1250" or "system_id":"310415" in a cell (including the "" ). Can I lookup up for that data even if it isn't exact?


Thanks again

[NBVC]

You can use wildcard...*

e.g

=COUNTIF(RANGE,"*"&A1&"*")

this would find 310415 (A1) in "system_id":"310415" (within RANGE)

[Elegidos]

Thanks for the quick reply but seems like it doesn't work for some reason.

[NBVC]

If you have somewhere in RANGE entered in a cell: "system_id":"310415"

and then you have 310415 entered in A1,

Then my formula,
=COUNTIF(RANGE,"*"&A1&"*"), works.. I tested it.

Can you post your sheet if you still have problems...

[Elegidos]

If you have somewhere in RANGE entered in a cell: "system_id":"310415"

and then you have 310415 entered in A1,

Then my formula,
=COUNTIF(RANGE,"*"&A1&"*"), works.. I tested it.

The formula works great when the number (310415) is surrounded by characters ("system_id":"310415"), but when the number is by itself in a cell doesn't seem to work. At this moment that isn't a problem, thanks.

Another question. In Condition Formatting, you can have only a max of 3 conditions. Is there a way to have 2 or more conditions in 1 condition and have different formats for the conditions?

Example:

Condition 1: =COUNTIF(range,A1)
Condition 2: =COUNTIF(range2,A1)

[NBVC]

Try this instead:

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,Range))))


You can combine formulas with AND() or OR() to determine if both conditions must be true or just one.

[Elegidos]

Ok thanks, but with the combine function how would it be? Like the following:


=COUNTIF(range,A1) and (range2,A1)

[NBVC]

=AND(COUNTIF(range,A1),COUNTIF(range2,A1))

for both to be TRUE...

=OR(COUNTIF(range,A1),COUNTIF(range2,A1))

for any one to be TRUE

[Elegidos]

Great help NBVC. Thanks agian.


This post solved. More coming up xD.

[Elegidos]

I have another question related with this topic.


I have 3 sheets. And in the sheet #2 is where I have the conditions. The only problem is that I have 2 conditions that do the same lookup: Condition 1 and 3.

Condition 1: Makes the text bold and colors the background cell to orange.
(from Sheet 3)


Condition 3: I want the text to become Italic and underline. But it seems that condition 1 has priority and this condition doesn't do nothing.
(from Sheet 1)

Both conditions from different sheets lookup the same information.

Any help please?


Thanks

[NBVC]

Please start a new thread as this is a new topic... thanks.