Vlookup / Lookup help

vcsush

Board Regular
Joined
Oct 1, 2006
Messages
52
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Oct 1, 2006
Messages
52
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Forum statistics

Threads
1,136,650
Messages
5,677,000
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top