Lookup and return multiple matches across multiple sheets

**somnath6309** · 09-07-2013, 01:20 AM

Dear Sir,

please find enclosed herewith an excel file that contains 13 sheets: the first one is the table that contains data to be looked up. the rest sheets are of the 12 months sheets that are to be looked up. In sheet 1 I have also inserted columns that are to be filled.

Kindly vide the attachment to understand and kindly reply.

Regards,
Somnath

**ashishraj** · 09-07-2013, 01:40 AM

you have data with merged cell first unmerged the cells then simply use vlookup
Try not using merged cells when you are collecting data

**rcm** · 09-07-2013, 01:56 AM

try this , I changed some of the seeked values to find matches

**Debraj Roy** · 09-07-2013, 02:15 AM

Hi Somnath..

Check this also..

Create a NameManager for SheetList consist all the sheet's Name..
then use below formula..

in SHeet1. B2.. Please confirm the formula by Ctrl + Shift + Enter. not just Enter..

Formula:

Please Login or Register  to view this content.

For more details check the file ..

Dhanyabaad.. aabar esho..

**ashishraj** · 09-07-2013, 02:29 AM

that's great but not understand clearly,
some explanation will more helpful.........

**Debraj Roy** · 09-07-2013, 02:44 AM

Hi Asish..

Lemme try..

Formula:

Please Login or Register  to view this content.

I think only color part is enough to elaborate..
1st search in which page the look value is residing..
So Count the Lookup Value in all sheet..
so.. lets say.. sheet 3 has a count > 0.. and all other has count = 0
{0,0,1,0,...} Now directly go to that sheet.. by Index + Match
Index of SHeet list, match sheet list, where count of lookup value is greater than 1
Only in that sheet.. use lookup table as "SHeet3" & "! A1:I100"

and leave the rest work for Vlookup.. function..

Let me know.. if you need more detail..
Happy to Help..
Your friendly neighborhood..

Lookup and return multiple matches across multiple sheets

LinkBack

Thread Tools

Rate This Thread

Display

Lookup and return multiple matches across multiple sheets

Re: Lookup and return multiple matches across multiple sheets

Re: Lookup and return multiple matches across multiple sheets

Re: Lookup and return multiple matches across multiple sheets

Re: Lookup and return multiple matches across multiple sheets

Re: Lookup and return multiple matches across multiple sheets

Thread Information

Users Browsing this Thread

Similar Threads

Lookup and return multiple matches across multiple sheets

[SOLVED] Lookup and Return Multiple Matches

lookup one value over multiple sheets & return multiple responses

Excel lookup for to return multiple approximate matches

[SOLVED] How do I use lookup to return multiple matches and sum them?

Bookmarks

Bookmarks

Posting Permissions