vlookup multiple worksheet question [SOLVED]

[rahulsharma012]

i am not getting the last 3 values.i dont know why.


here is the link


http://www.fileconvoy.com/dfl.php?id=g6398dc0bf3cb5bbf9992930981a31304ad22e42de


thanks,

rahul

 

[NARAYANK991]

Hi Rahul ,


Can you check your file here ?


http://www.fileconvoy.com/dfl.php?id=gb752319c9b0a417e999293117846a5deb2b7b7cfa


Narayan


P.S. If your problem is solved , please confirm.

 

[rahulsharma012]

thanks narayan problem solved.


now just want to know can we cut short the formula in the description column with the use of indirect and vlookup only.


i might have a feeling that the formula can be cut short to somewhat minimum and easy to understand.


thanks

rahul

 

[NARAYANK991]

Hi Rahul ,


I am not so sure ; if you see the formula :


=VLOOKUP(A4,INDIRECT("'"&INDEX(sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&sheetlist&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),2,FALSE)


it is following the syntax of a VLOOKUP function , which is :


=VLOOKUP(value , range address , column number , TRUE or FALSE)


Here , the range address is arrived at by :


INDIRECT("'"&INDEX(sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&sheetlist&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4")


We have a named range called sheetlist , which contains the names of all the concerned sheet tabs.


The MATCH function tells us which entry in this named range ( i.e. which sheet tab ) has the value we are looking for ; the COUNTIF function returns an array of values , which will have 1 if the value was found in a particular sheet tab , and 0 if it was not found e.g. {1;0;0;0} if the value was found on the sheet tab labelled Sheet2 , {0;1;0;0} if the value was found on the sheet tab labelled Sheet3 , and so on.


The only simplification can be this :


MATCH(1,COUNTIF(INDIRECT("'"&sheetlist&"'!$A$1:$c$4"),A4),0)


instead of this :


MATCH(1,--(COUNTIF(INDIRECT("'"&sheetlist&"'!$A$1:$c$4"),A4)>0),0)


This is valid provided the value being looked up does not occur more than once on a sheet tab.


Narayan

 

[rahulsharma012]

OK ANY WAY THE BIG THING IS THAT OUR PROBLEM GET SOLVED


THANKS A LOT ONCE AGAIN

 

[prajithp]

Hi Narayan


I didnt understand how you created the "sheetlist".Also would like to know if data are more in one sheet and less in other sheet. will this formula work.


INDIRECT("'"&INDEX(sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&sheetlist&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4")

 

[NARAYANK991]

Hi Prajith ,


sheetlist is a named range i.e. it is a range which has been given a name. In this case , it refers to the range Sheet1!$H$3:$H$6.


What this range contains is the names of the sheets which have the data tables in them viz. Sheet2 , Sheet3 , Sheet4 and Sheet5.


The range $A$1:$C$4 is a pre-defined range , which contains data ; this is the same on every one of the sheets Sheet2 , Sheet3 ,... Since it is specified within the formula , there is no way that it can vary from one sheet to another ; what you can do is use the largest possible range e.g. $A$1:$C$577. Even if cells in this range are blank , it should not matter. Where ever the value being looked up is present , the corresponding data from that sheet will be returned.


Narayan