Vlookup / Lookup help

[vcsush]

Hi All,
First up, thanks for any/all help. I have found generous helpers on this board before....so here's hoping............

I have a sheet with a list of names in the column A. I need a vlookup / or any formula which will search for the name in a whole other sheet instead of just a column (as in vlookup) and return a "present" or whatever in case it is found anywhere in the sheet.

Since I have to search for all names, I could not manually put in the names each time I want to find them. It would be great if it could be dragged down like a formula.

It is actually a system for marking attendance for people who log in.

Thanks again guys
Vcsush

 

[NBVC]

Try:

=IF(OR(ISNUMBER(SEARCH(A2,Sheet2!$A$2:$K$21))),"present","absent")

Where Sheet2!A2:K21 is the range housing the various names

Adjust range and sheetname as necessary. Then confirm the formula with CTRL+SHIFT+ENTER not just ENTER. You should see {} brackets around the formula if entered correctly.

 

[vcsush]

Genius........Pure Genius.....................Thanks a bunch man......Owe you....

Now how would I get it to search across all three sheets for the one entry instead of just searching in one sheet????????

Also would really appreciate if you would explain your formula.....I understood the search part, but didn't really get the OR part or the ISNUMBER part? Why are they necessary?????

Even if you don't answer.......
Thanks again

Vcsush

 

[Domenic]

Let B2:B4 contain the sheet names, then try...

=IF(ISNUMBER(MATCH(TRUE,COUNTIF(INDIRECT("'"&$B$2:$B$4&"'!A2:K21"),A2)>0,0)),"Present","Absent")

...confirmed with CONTROL+SHIFT+ENTER. Note that if the lookup value in A2 can be contained within any text string in A2:K21, use the following formula instead...

=IF(ISNUMBER(MATCH(TRUE,COUNTIF(INDIRECT("'"&$B$2:$B$4&"'!A2:K21"),"*"&A2&"*")>0,0)),"Present","Absent")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[NBVC]

Now how would I get it to search across all three sheets for the one entry instead of just searching in one sheet????????

A couple of other options:

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&$B$2:$B$4&"'!A2:K21"),A2)),"present","absent") where B2:B4 contain a list of sheetnames for the sheets you want to search though. (Note: you can also name the range containing the list and use that name in the formula).

... which does not need to be confirmed with the CSE key combo. Just Enter will suffice.


If it is only 2 or 3 sheets, you can still use the formula I previously provided and just add another condition in the OR() function:

e.g. (with 2 sheets)
=IF(OR(ISNUMBER(SEARCH(A2,Sheet2!$A$2:$K$21)),ISNUMBER(SEARCH(A2,Sheet3!$A$2:$K$21))),"present","absent")

....which will need to be confirmed with the CSE key combo.

Also would really appreciate if you would explain your formula.....I understood the search part, but didn't really get the OR part or the ISNUMBER part? Why are they necessary?????

The Search() part returns a numeric result (1) if a match is found, else it returns #Value! error. The ISNUMBER() check to see if a numeric result is found. The OR() function checks each cell in the range stated for a numeric result. Once one numeric result is found, a TRUE is returned resulting in the "present" string being returned. If no numeric results found, the "absent" is returned.