vlookup from multiple work sheets in excel 2003.

[Mohammad Rashed Iqbal]

I have a large number of data in 20 different worksheets of a single excel file 2003.

Like:
In Sheet1
Customer ID Sales
32438-1 500
33659-1 900
15436-9 1300
3242-1 1700
13242-1 2100
So many data
In Sheet2
Customer ID Sales
3011534 2500
30840-1 2900
31198-1 3300
2226-1 3700
30399-1 4100
30398-1 4500
So many data

In Sheet3
Customer ID Sales
30396-1 4900
13422-1 5300
15679-1 5700
15667-1 6100
15666-1 6500
15664-1 6900
So many data
In Sheet4
Customer ID Sales
15663-1 7300
15661-1 7700
15621-1 8100
13466-1 8500
15651-1 8900
14572-1 9300
So many data

Now I want to lookup sales of some customer ID in Sheet21, like

Customer ID Sales
3242-1 ??
15661-1 ??
30399-1 ??
13422-1 ??
15679-1 ??
14572-1 ??
14377-1 ??
So many data ??

How can I insert the vlookup function in sheet21? Please any body kindly let me know with detail.

 

[vletm]

Mohammad Rashed Iqbal
Why those 1..20 sheets?
Combine those to one sheet and
after that some things would be more useful.

 

[bosco_yip]

Try…..............

1] In "Sheet21" B2, formula copied down :

=IFERROR(VLOOKUP(A2,INDIRECT("'"&INDEX("Sheet"&ROW($1:$20),MATCH(TRUE,INDEX(COUNTIF(INDIRECT("'"&"Sheet"&ROW($1:$20)&"'!A2:A100"),A2)>0,0),0))&"'!A2:B100"),2,0),"")

2] See attached file

Regards
Bosco

 

[rahulshewale1]

hii @Mohammad Rashed Iqbal ,

Or...

Name Manager :
=OFFSET('my solution'!$J$2,,,COUNTA('my solution'!$J$1:$J$6)-1,1)

B3:
=VLOOKUP(A3,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$B$7"),A3)>0),0))&"'!$A$2:$B$7"),2,FALSE)



Regard
Rahul shewale