Find Value Anywhere in another worksheet

[roxsingh]

Hello,

I am looking to find a value from one column e.g. 'Jim Horton' in Column A in worksheet 1 and look to see if that value 'Jim Horton' exists anywhere in several columns say Column A,B,C,D,E,F etc in worksheet 2. Where a value exist i want the values highlighted or placed in another column in Worksheet 1.

Can you please assist?

[Tony Valko]

What version of Excel does this have to work in?

[roxsingh]

Either Excel 2007 or Excel 2016

[roxsingh]

What version of Excel does this have to work in?

Either Excel 2007 or Excel 2016

[Tony Valko]

Try this...

Sheet2

	A	B	C	D
1
2	Data	Data	Data	Data
3	Data	Data	Data	Data
4	Data	Data	Data	Data
5	Data	Data	Data	Data
6	Data	Name1	Data	Data
7	Data	Data	Data	Data
8	Data	Data	Data	Data
9	Data	Data	Data	Name5
10	Name7	Data	Data	Data

Sheet1

	A	B
1
2	Name1	Name1
3	Name2	Name5
4	Name3	Name7
5	Name4
6	Name5
7	Name6
8	Name7
9	Name8
10	Name9

This array formula** entered in B2:

=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(Sheet2!A:D,A$2:A$10),ROW(A$2:A$10)),ROWS(B$2:B2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[roxsingh]

Try this...

Sheet2

	A	B	C	D
1
2	Data	Data	Data	Data
3	Data	Data	Data	Data
4	Data	Data	Data	Data
5	Data	Data	Data	Data
6	Data	Name1	Data	Data
7	Data	Data	Data	Data
8	Data	Data	Data	Data
9	Data	Data	Data	Name5
10	Name7	Data	Data	Data

Sheet1

	A	B
1
2	Name1	Name1
3	Name2	Name5
4	Name3	Name7
5	Name4
6	Name5
7	Name6
8	Name7
9	Name8
10	Name9

This array formula** entered in B2:

=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(Sheet2!A:D,A$2:A$10),ROW(A$2:A$10)),ROWS(B$2:B2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Thank You very much it worked....i am trying to understand it though...will do some research as well...but can u explain also...
This was the initial thing so i may need to do some modifications.

There may be modifications of how the data may appear in the sheets...eg. sheet1 may have the whole name but sheet2 may have the whole name split into separate columns e.G. first, second, last which may increase according to the number of names (the only option i see though is doing a concatenation in sheet 2 to aid with this...but do u suggest any other method that may seem more 'automatic' and can be included in the existing function...

It would be great if u can also direct me to great links to help me in understanding and mastering excel formulas...

Thanks so much for your speedy response

[Tony Valko]

There may be modifications of how the data may appear in the sheets...eg. sheet1 may have the whole name but sheet2 may have the whole name split into separate columns e.G. first, second, last which may increase according to the number of names (the only option i see though is doing a concatenation in sheet 2 to aid with this

Yes, that sounds like your best option.

It would be great if u can also direct me to great links to help me in understanding and mastering excel formulas...

I think your best opportunity to learn more about Excel is to "hangout" in forums like this one. Read the posts and study the solutions. Do lots of experimentation. You'll learn a lot by trial and error.

[roxsingh]

Yes, that sounds like your best option.


I think your best opportunity to learn more about Excel is to "hangout" in forums like this one. Read the posts and study the solutions. Do lots of experimentation. You'll learn a lot by trial and error.

Thanks much 'Tony Valco'

[Tony Valko]

You're welcome!