How to do multi-column vlookup using multiple workbooks?

[0Cool]

I have 3 workbooks (Adata.xlsx, Bdata.xlsx and Cdata.xlsx) with similar row and column structures like each of the workbooks have the following columns ID, Name, Basic, DA, HRA, Gross, Incentive

Now in another workbook, if I enter a ID value then judging by the first letter of the value (e.g. A, B or C), how do I fetch the information of say, Basic, Gross using vlookup or any other function from appropirate workbook?

I've done

Please Login or Register  to view this content.

It works, but I'm not sure whether this is the right approach to this problem... Does anyone have a better process for this?

[avk]

Can you attach your another workbook with enter manually required output.

[0Cool]

Can you attach your another workbook with enter manually required output.

Did so...you can check it out now

[AliGW]

No, you didn't. This is all you have given us:

Excel 2016 (Windows) 32 bit

	A	B	C
1	ID	Basic	Gross
2	B005

Sheet: Sheet1

This is not enough to show what you are wanting. Make a bit more of an effort, please, to provide the sample data requested.

[avk]

One way with using all file name.
Create data validation list in other file (in "G2")
In "A2"

Please Login or Register  to view this content.

In "B2"

Please Login or Register  to view this content.

In "C2"

Please Login or Register  to view this content.

Copy paste down as per data file range.
Attach all 4 files.
[Kindly change path as per your folder]

[0Cool]

Hi AliGW,

What I want is to enter an ID or multiple ID's in the other.xlsx (e.x. C009) value and the function should identify which of the workbooks has the matching ID value in (e.g. Cdata.xlsx as the ID value starts with 'C') and then get the value of Basic and Gross from that workbook and paste it in the other.xlsx Basic and Gross fields.

Basically the other.xlsx should look something like
Untitled.png

Hope I made it more clear for you

[avk]

Dear Cool : Have you gone through my post 5#

[0Cool]

Hi avk,

yes I have gone through your post, but it is not what I was looking for.
Have you gone through my post 6# ?