Hello,
I am tring to figure out the functions to use in order to look up data in 2 (or more) sheets and populate a new sheet with the required data. In the example I attached, the First sheet (Company Accidents) contains data on what employee was injured, whether it was avoidable or not and if they passed a drug test. We may filter this sheet's data in one or more columns based on the report requested by the client. Also, this sheet's column order may change the next time it is supplied to us and is beyond our control. The order the columns are in this time may not be the same the next time but the overall sheet will have the same column headings. BUT let's assume the employee number column will always be in column A (and if it is not, I can move it to column A)

The Second sheet (Employee Data) contains the employee's date of birth, number of accidents the employee has had and so on. This data's column order may change too but will have the same column heading names. Again, let's assume the employee number column will always be in column A (and if it is not, I can move it to column A). This data will not be filtered but will be very lengthy, probably over containing over 150 employees.

The Third sheet (2012 Accident Data) is the sheet I want to generate. I would want each employee number appearing in the first sheet to appear by formula on the 3rd sheet and based on the employee number, fill in the rest of the values requested for the 3rd sheet. Since the column order may change I did not think that Vlookup would work. What would I use to generate the 3rd sheet? Is this possible to do using formulas or would it be better to use VBA? I did something similar to this before and it did work but it took a lot of time to tediously set up the tables in excel to make the Vlookup work properly.



Any help is very much appreciated,

Rich

Attached Files

Need to know how to look up data in many sheets and combine in one sheet.xlsx (10.1 KB, 14 views)

Hi Rich,

you can nest a match function inside a vlookup to work out the column number for you.

So a simple vlookup would look like this:
=VLOOKUP(A2,'Company Accidents'!A1:H6,2,0)

with a nested match function:
=VLOOKUP(A2,'Company Accidents'!A1:H6,MATCH(B$1,'Company Accidents'!$A$1:$H$1,0),0)

and finally to fix any errors if not found, add an iferror function:
=IFERROR(VLOOKUP(A2,'Company Accidents'!A1:H6,MATCH(B$1,'Company Accidents'!$A$1:$H$1,0),0),"")

Please see attached solution

Attached Files

Vlookup with Nested Match.xlsx (10.5 KB, 24 views)

Craig,
Thank you very much for replying. I didn't think of doing that! I will do some experimenting with the nested match function and will post back here ASAP.
Rich

No Problems Rich. Please mark as Solved if you don't have any further questions

Been up to my eyes in another project and will consider this question answered. Thank you very much Craig for your help.

Rich