Kind of reverse vlookup

Hi guys,

I am trying to create a formula that would save a lot manual work that. I've already tried this with VLOOKUP and MATCH&INDEX but I can't get the results i want.

Let's say I get a large set of data every week that I want to divide to different excel files (one file with one persons data). The masterdata will have the same columns every week, but the row's that belong to each person will variate every week. There are also a lot of data (columns) I don't want to include in the individual files. Originally I created a .bat file that would create template's for every person, and in the templates I tried to use vlookup and match/index formulas to grab the data from my masterdata excel as I open the individual files, but I didn't get it to work. Now I have no idea what I should try next, probably this should be done with macros/vba (as far as automatically creating individual excels per person goes at least..) but I have no experience with them.

I have an example attached about what I would like to achieve.

In the beginning I'd only have the sheet that's called Masterdata and what I would like to achieve (have in the end) are now in PersonX.xls sheet's which should really be separate files. In real life there are way more colums and data in the masterfile and way more individual excel's to be created. So im trying to find a shortcut so I wouldn't have to do all of copying, pasting and sorting.

I've been browsing this forum for help since I did my master's thesis a bit over a year ago and i've noticed there are a lot of really advanced excel users here who can do almost anything so I wanted to give this a try.

Thanks!

Hi PettOne,

To add to Jac's post, the personal nos. which you are trying to fetch info are not unique. I would suggest you to use the first column nos., to fetch the data through index+match.

In the attachd sheet if you put the numbers (which is a unique value) for a person in col A in all the sheets and drag the formula right and down then you would, get the values. Please keep the column names same. The data range is also adjusted on its own.

Thanks,
Bhuvi


<if my answer helped, please click on the * Star icon below this post to add some reputation.>

Well in the example file file I have included Numbers, Letters, People, Dates as values i want to include in the individual files in the end. So in the beginning i would only have empty templates with the columns i want to include, and i would want to look up the data for those columns from the master data. In real life the column's would be more like: euros, different reference numbers, companies etc, and they are all unique.

When i tried this with vlookup I had the same return value (cell) in every cell of the data when i rolled down the formula (and for that i had to sort the master data to have the persons name in the first column, which is something I would also like to avoid).

When I tried with match/index/row it included also other person's data as return values.

But in short regarding the attached excel: I want to lookup the person's names from the data and include data in columns Numbers, Letters, People, Date, and it should look it up by the names in People -column

Check this out, this is not a perfect solution but if u hv 5-6 sheets where u want to import the data then it will help u perfectly...

what i did i just give the rangename to each table in which u want to import the data and use this formula
=IFERROR(VLOOKUP(A2,table2,3,FALSE),IFERROR(VLOOKUP(A2,table3,3,FALSE),(VLOOKUP(A2,table4,3,FALSE))))

Note: this function will wrk on limited sheets only




example.xlsx

In the attached sheet post#3, you could add on more columns throughout the sheets, the formula would fetch values whenever the column names are same across all the sheets.

Sorry for the previous post I did't undrstnd the que.

so check this file hope this is what u want




example_2.xlsx

The problem is that in the beginning i only have the masterdata, and the person1,2... sheet's should actually be their own individual excel files(there will be ~25 every time with the same persons names) and not sheet's in the masterfile.

Only static data I will have is the person's names in one column, and all the other data is unique (euros, different references, cities, date's etc). Im sorry that my example was a bit unclear, as the number's are not ruolling (1,2,3,4..) in real life and there are no columns with static numbers

Thanks Jacc and pwnyadav007! I think I can do it with either one of these. I can probably just fetch the master data to an other template where I have the individual sheet's, then I will just have to look up something to automatically create the individual excels from the individual sheets! Does someone know how to manage something like that?

yes Jacc is right , just keep one thing in the mind after moving sheets in new workbook,afterthat don't change the location of master sheet otherwise it will create an error to your formula sheets

and jacc formulas is better than me so adopt that