how to get Cell reference as Vlookup result of a different sheet

[TKD]

In workbook 1, I have summary sheet with columns
A -> SNO
B -> Customer Name
C -> Product Name
D -> MODEL
E -> Quotation Rate
I have to retrieve MODEL & Quotation Rate .
and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1)
In X1 sheet , I do have following columns
A-> Product Name ,
b-> Model (as of now limited to 1 per product name)
c -> Rate

Now my request is as follows
Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D.
I heard i can use INDIRECT function and Vlookup in this junction .
Can you kindly help me in this

 

[davesexcel]

Maybe this can help
How to use Index/Match - davesexcel.com

 

[Marcelo Branco]

Maybe

Summary sheet

D2
=VLOOKUP($C2,INDIRECT("'"&$B2&"'!A:C"),2,0)
copy down

E2
=VLOOKUP($C2,INDIRECT("'"&$B2&"'!A:C"),3,0)
copy down

M.

 

[TKD]

D2 gets only #NA value and nothing more..
Will try index and match now

 

[TKD]

The link provided is very informative . But unable to find reference on how to combine this with indirect as i had to use this with dynamic sheet reference.
Can you kindly give example of this.

 

[Marcelo Branco]

D2 gets only #NA value and nothing more..
Will try index and match now

It worked for me.

Column B (Customer Name) does contain names identical to sheet names?

For example for customer X1 (value in B2) does exist a sheet named X1?

M.