• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can we use VLOOKUP to check multiple sheets for 10 different sheets in fact.

R

rahulsharma012

New Member
The idea being that if VLOOKUP doesn't find a match on the first sheet, it will check the next sheet and so on.


like if in sheet1 we have product code aand price columns with 100 product codes given.


next we have 10 sheets from sheet2 to sheet11 with product code aand price columns duly filled.So i want to put vlookup in price column of sheet1 to check if the product code in sheet1 is in sheet2 if not then it will check for sheet3 likewise till sheet11.


if product code matches in any sheet it will display the respective price in sheet1 column of price and if not then it will display not found.


PLEASE I WANT THE FORMULA SHORTEST AS POSSIBLE.


thanks

Rahul
 
Hi Rahul ,


Try this :


=IFERROR(VLOOKUP(A1,INDIRECT("'"&INDEX(List_of_Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&List_of_Sheets&"'!"&Data_Range),A1)>0,0))&"'!"&Data_Range),1,0),"Not Found")


List_of_Sheets : this is a range where you list all the Sheet Names that you want searched for the product code e.g. in cells $K$1:$K$3 you can have the sheet names Sheet1 , Sheet2 and Sheet3.


Data_Range : is the range in each sheet ( the range should be the same in all sheets ) where the value in A1 will be looked for e.g. you can have values in $F$1:$F$10 in each sheet


A1 has the value you want to look up in the range $F$1:$F$10 in each of the sheets Sheet1 , Sheet2 and Sheet3.


I have used 1 as the third parameter for the VLOOKUP function ; if you define Data_Range with several columns , then use the column number from which you want the price to be retrieved , instead of 1.


Narayan
 
Hi Rahul,


Although Narayan(one of the best formula evaluator of this forum) has explained the formula very well, I would still like to share the below link in case you want to read/study and understand the in depth mechanism of such formula:


http://www.myonlinetraininghub.com/excel-vlookup-multiple-sheets


Regards,

Kaushik
 
Hi Narayan and kaushik,


the formula given by you,i pasted it exactly but its not working.Their is no error with the list of sheets and data range names.i had changed them accordingly but its still not working and showing not found for all the values.
 
Hi Rahul ,


I forgot to mention that the formula is an array formula , to be entered using CTRL SHIFT ENTER. Sorry.


Narayan
 
brother i am executing it with ctrl+shift+enter but i dont know its still not working.i cannot find any way to send as attachment the excel file so that it will give you the exact scenario.if you have any mail id please tell me so that i can send you the excel file.


thanks,

Rahul
 
Hi Rahul ,


You have used names such as ListofSheets ; these are named ranges which need to be defined using the name Manager menu option in Excel.


Check out your file here :


http://speedy.sh/MvngK/rahul-1.xlsx


Narayan
 
narayan,


plz send the link to hotfile because i am in office and their is proxy security,so i cant have access to all sites.so plz send the hotfile.com link.


thanks,

rahul
 
hi narayan & kaushik,


thanx a lot for your continuing replies.


the only thing in my mind is that what data_range denotes in the formula exactly.is data_range is also a named range.


can i use $a$1:$c$4 in place of data_range.


thanks,

Rahul
 
Hi Rahul ,


Sure. You can use static cell addresses instead of range names ; however ,


1. named ranges can be dynamic ; incorporating this feature using static cell addresses in your main formula will make the formula quite lengthy and difficult to understand


2. using range names makes the formula more readable and understandable


3. You will find that with repeated usage , defining named ranges becomes a habit !


Narayan
 
hi narayan,


i am unable to figure out how you gave named range to data_range.I think for each sheet their will be a different name of data_range.


how you did it brother.


plz clarify.


thanks,

Rahul
 
Hi Rahul ,


This is a trick which I learnt from the Internet , I forget where.


When you define a named range , using Excel's Name Manager , you have the sheet name included in the range definition e.g. Data_Range may refer to =Sheet1!$E$12:$E$18


This now defines the name Data_Range to refer to this range of addresses on the Sheet1 tab.


However , if you remove the sheet name from the above definition , and make it =!$E$12:$E$18 , you can use this name Data_Range with any sheet name , and it will refer to the range $E$12:$E$18 on that sheet tab e.g. on Sheet1 , it will refer to Sheet1!$E$12:$E$18 , on Sheet2 , it will refer to Sheet2!$E$12:$E$18 and so on.


Of course , you have to remember that the range address ( $E$12:$E$18 ) will remain the same across all worksheets.


Narayan
 
You must log in or register to reply here.
Back
Top