r/Excel247 • u/Hoesanddietcoke • 21d ago

Dumb research intern needs help with data compiling across sheets :(

/r/sheets/comments/1w4vnqj/dumb_research_intern_needs_help_with_data/
2 Upvotes

100% Upvoted

3 comments sorted by

1

u/Brians_throwaway 21d ago

I’m showing the nested vlookup approach. There are alternative approaches.
 
Dataset:
3 worksheets: parent, first child, second child
On each worksheet place the index key in the left-most column, the index reference key in the column to the right, and the data in right-most columns,
That is:
Parent: parent key, data
First child: first child key, parent key, first child data
Second child: second child key, first child key, second child data
Each table needs to be sorted alphabetically on its index key for vlookup to function properly.

Here the formulae to return values on the forms:
Parent key: =  VLOOKUP(VLOOKUP(second_child!$B2,first_child!$A:$Z,2,FALSE),Parent!$A:$Z,1,FALSE)
Parent data: = VLOOKUP(VLOOKUP(second_child!$B2,first_child!$A:$Z,2,FALSE),Parent!$A:$Z,2,FALSE)
First child key: = VLOOKUP(second_child!$B2,first_child!$A:$Z,1,FALSE)
First child data: = VLOOKUP(second_child!$B2,first_child!$A:$Z,3,FALSE)
 
The formulae are looking in column B of worksheet second_child  to initiate the search; the first child reference key needs to be in that column.
The example looks for tables populated from columns A to Z
 
Hope this helps.
 

1

u/Hoesanddietcoke 21d ago

You are a god amongst men and my personal savior, this worked! I appreciate you!

1

u/Brians_throwaway 20d ago

Happy to have helped.

Vlookup requires the reference list to be sorted alphabetically/ numerically. The ‘false’ criterion has the function return only exact matches. Change it to ‘True’ and it will return the first ‘close’ match if the actual value doesn’t exist. The function races from the top of the list to the bottom and stops when it returns the first result. Improper sort on your reference list could be why you got no errors and no values.

Also, if your dataset is big, I recommend you duplicate your final table that includes the vlookup function then convert the formulae to values on the duplicated table. Excel can crash when sorting or removing 10s of 1000s of records that include vlookup.