HELP! Lookup, countif or if statement
 

Hello

I have a "raw data" sheet of 1500 rows and 30 columns.... in columns J, K,
L, M, T, W are names of people. In the "Lookup" sheet I have a list of
leavers (I have defined the list). In the last column of the "Raw Data" sheet
I need to bring back the Name/s of the leaver/s if mentioned in columns J, K,
L, M, T, W.
I know I need to use a nested formula just which ones?

You help is greatly appreciated!!

HELP! Lookup, countif or if statement
 

Suppose your list of leavers names is in column A of Sheet2 in the
same workbook. You can have a formula like this to check if the name
in J2 is in your list of names:

=IF(ISNA(MATCH(J2,Sheet2!$A:$A,0)),"",J2)

You can apply the same type of formula to look in K2, L2 etc and join
the results together, but you will probably want them separated (eg
with a space), like this:

=TRIM(IF(ISNA(MATCH(J2,Sheet2!$A:$A,0)),"",J2&" ")&IF(ISNA(MATCH
(K2,Sheet2!$A:$A,0)),"",K2&" ")&IF(ISNA(MATCH(L2,Sheet2!$A:$A,
0)),"",L2&" ")&IF(ISNA(MATCH(M2,Sheet2!$A:$A,0)),"",M2&" ")&IF(ISNA
(MATCH(T2,Sheet2!$A:$A,0)),"",T2&" ")&IF(ISNA(MATCH(W2,Sheet2!$A:$A,
0)),"",W2))

The TRIM function gets rid of any multiple spaces in the resultant
string.

Hope this helps.

Pete

On Nov 25, 11:27*am, Tracey wrote:
Hello

I have a "raw data" sheet of 1500 rows and 30 columns.... in columns J, K,
L, M, T, W are names of people. In the "Lookup" sheet I have a list of
leavers (I have defined the list). In the last column of the "Raw Data" sheet
I need to bring back the Name/s of the leaver/s if mentioned in columns J, K,
L, M, T, W.
I know I need to use a nested formula just which ones?

You help is greatly appreciated!!

HELP! Lookup, countif or if statement
 

Pete you are a God send!!!!! Thank you, it works a treat!



"Pete_UK" wrote:

Suppose your list of leavers names is in column A of Sheet2 in the
same workbook. You can have a formula like this to check if the name
in J2 is in your list of names:

=IF(ISNA(MATCH(J2,Sheet2!$A:$A,0)),"",J2)

You can apply the same type of formula to look in K2, L2 etc and join
the results together, but you will probably want them separated (eg
with a space), like this:

=TRIM(IF(ISNA(MATCH(J2,Sheet2!$A:$A,0)),"",J2&" ")&IF(ISNA(MATCH
(K2,Sheet2!$A:$A,0)),"",K2&" ")&IF(ISNA(MATCH(L2,Sheet2!$A:$A,
0)),"",L2&" ")&IF(ISNA(MATCH(M2,Sheet2!$A:$A,0)),"",M2&" ")&IF(ISNA
(MATCH(T2,Sheet2!$A:$A,0)),"",T2&" ")&IF(ISNA(MATCH(W2,Sheet2!$A:$A,
0)),"",W2))

The TRIM function gets rid of any multiple spaces in the resultant
string.

Hope this helps.

Pete

On Nov 25, 11:27 am, Tracey wrote:
Hello

I have a "raw data" sheet of 1500 rows and 30 columns.... in columns J, K,
L, M, T, W are names of people. In the "Lookup" sheet I have a list of
leavers (I have defined the list). In the last column of the "Raw Data" sheet
I need to bring back the Name/s of the leaver/s if mentioned in columns J, K,
L, M, T, W.
I know I need to use a nested formula just which ones?

You help is greatly appreciated!!

.

HELP! Lookup, countif or if statement
 

You're welcome, Tracey - thanks for feeding back.

Pete

On Nov 25, 1:01*pm, Tracey wrote:
Pete you are a God send!!!!! Thank you, it works a treat!



"Pete_UK" wrote:
Suppose your list of leavers names is in column A of Sheet2 in the
same workbook. You can have a formula like this to check if the name
in J2 is in your list of names:

=IF(ISNA(MATCH(J2,Sheet2!$A:$A,0)),"",J2)

You can apply the same type of formula to look in K2, L2 etc and join
the results together, but you will probably want them separated (eg
with a space), like this:

=TRIM(IF(ISNA(MATCH(J2,Sheet2!$A:$A,0)),"",J2&" ")&IF(ISNA(MATCH
(K2,Sheet2!$A:$A,0)),"",K2&" ")&IF(ISNA(MATCH(L2,Sheet2!$A:$A,
0)),"",L2&" ")&IF(ISNA(MATCH(M2,Sheet2!$A:$A,0)),"",M2&" ")&IF(ISNA
(MATCH(T2,Sheet2!$A:$A,0)),"",T2&" ")&IF(ISNA(MATCH(W2,Sheet2!$A:$A,
0)),"",W2))

The TRIM function gets rid of any multiple spaces in the resultant
string.

Hope this helps.

Pete

On Nov 25, 11:27 am, Tracey wrote:
Hello

I have a "raw data" sheet of 1500 rows and 30 columns.... in columns J, K,
L, M, T, W are names of people. In the "Lookup" sheet I have a list of
leavers (I have defined the list). In the last column of the "Raw Data" sheet
I need to bring back the Name/s of the leaver/s if mentioned in columns J, K,
L, M, T, W.
I know I need to use a nested formula just which ones?

You help is greatly appreciated!!

.- Hide quoted text -

- Show quoted text -