How to use Vlookup in different sheets using a single formula

[rush2rajen]

Hi Excel Gurus,

I need a help on how to use Vlookup.

There is one VLOOKUP sheet from where the data needs to be taken and there are 6 different sheets namely AA,BB,CC,DD,EE,FF.

I want a formula in AA to FF sheets in Case Column where it will capture data from VLOOKUP sheet of their own name like AA will take data from VLOOKUP sheet only of AA.( and not from BB,CC,DD,EE,FF)

I think I was able to clarify the need.
Sample file attached.

Thanks
Raj

 

[Syedali]

I tried my level best. Please find the attached answer. Kindly let me know if you have any clarification.

 

[json]

Question: it appears that you have the same list on all sheets AA-FF, correct? so this will result in a lot of rows where no result will be returned.


would it be better/easier to have a pivottable for the AA, BB, CC, etc. data?

 

[Somendra Misra]

Hi Raj,

Try below formula in C2 and copy down:

=SUMPRODUCT(--(INDIRECT("'"&A2&"'!A2:A243")=B2))

Regards,

 

[rush2rajen]

Thanks Misraji