Help with excel formula to match data from one sheet to another

Question

I am trying to create a formula to search for one cell (for example, in 1st picture 96) anywhere in another sheet (2nd picture). I would like to then apply this formula to all the cells in one column. For example, in sheet1 cell 6, search all of sheet2 column A and if 96 exists in sheet2 then return true or format yellow, etc. Then I would like to do the same formula to for each cell in sheet1: does this cell value exist in sheet2, if so highlight yellow.Using excel 2016, I've tried vlookup but cannot get it to work. Thank you.

Image upload not working. Linked here:

https://files.catbox.moe/3orabd.png

https://files.catbox.moe/c5s2yz.png

Answer 1

Hi Jason

You need to remove the $ sign before the 6 in your formula,

Try the formula

=ISNUMBER(MATCH(A6,Sheet2!$A$1:$A$10,0))

Regards

Jeovany

Answer 2

I think it's working, thank you!!! Do you know how to have it also put a word in the cell if the value is found? For example, if the cell value is found in sheet 2 return the word TRUE and highlight yellow? I got it to fill/highlight the blank cell. Thank you again.

Then use the same formula I gave you in the cells adjacent to the value.

Regards

Jeovany