SOLVED: Google Sheets: Using VLOOKUP across multiple sheets using INDIRECT, INDEX, and MATCH with a named range of sheets

0
votes

I have multiple sheets that will be named by the end user. The name of those sheets is stored in a named range that is also updated by the end user. Each of those sheets will contain data that needs to be pulled into a master sheet. Let's provide them some names for clarity:

  • Sheet MASTER contains the pulled data
  • Range PEOPLE contains the sheet names
  • Sheet Jones, Roy, for example, contains the data to be pulled, and that name exists in PEOPLE (there is an unspecified number of named sheets in PEOPLE)

There exists a method of doing this in Excel, but the formula doesn't work properly in Google Sheets. The formula is written to perform VLOOKUP on each sheet from PEOPLE using a search_key from MASTER and pull data from column 5 (in this example) of the specified sheet if there's a match. When I use the Excel formula, it will only pull the data from the first name listed in the range PEOPLE.

The formula is:

=IFERROR(VLOOKUP($A4,INDIRECT("'"&INDEX(PEOPLE,MATCH(1,--(COUNTIF(INDIRECT("'"&PEOPLE&"'!$A$3:$L$50"),$A4)>0),0))&"'!$A$3:$L$50"),5,FALSE),"")

What am I doing wrong here? As I stated, it performs the operation, successfully, for the first name in PEOPLE, but stops there. Any suggestions would be greatly appreciated.

https://docs.google.com/spreadsheets/d/1L1U1OwuaVSCt5n5jrUUImA6DQfnn8BWIOQKJlqelDtw/edit?usp=sharing

1
google-sheetsgoogle-sheets-formula
Please try use of importrange function that might solve your problem – Harsh
share a copy of your sheet – player0
So you basically want to change the name of the sheets depending on the value you set in a specific range (people) right? A sample spreadsheet indicating you actual vs desired out could help to clarfiy your question. Thanks ! :D – Mateo Randwolf
@Harsh importrange is for external ranges, the desired ranges are in the same sheet – Sean Reeves
@player0 I will have to make a dummy copy because I don't want anyone ripping off my sheet. It's going to be shared, eventually, but not until it works as intended. – Sean Reeves

1 Answers

0
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, {
 INDIRECT(PEOPLE!A2&"!A:F");
 INDIRECT(PEOPLE!A3&"!A:F");
 INDIRECT(PEOPLE!A4&"!A:F");
 INDIRECT(PEOPLE!A5&"!A:F")}, COLUMN(B:F), 0)))

enter image description here

update:

hardcoded version for 250 sheets regardless if they exist or not

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, {
IF(PEOPLE!A2<>"", IFERROR(INDIRECT(PEOPLE!A2&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A3<>"", IFERROR(INDIRECT(PEOPLE!A3&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A4<>"", IFERROR(INDIRECT(PEOPLE!A4&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A5<>"", IFERROR(INDIRECT(PEOPLE!A5&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A6<>"", IFERROR(INDIRECT(PEOPLE!A6&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A7<>"", IFERROR(INDIRECT(PEOPLE!A7&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A8<>"", IFERROR(INDIRECT(PEOPLE!A8&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A9<>"", IFERROR(INDIRECT(PEOPLE!A9&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A10<>"", IFERROR(INDIRECT(PEOPLE!A10&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A11<>"", IFERROR(INDIRECT(PEOPLE!A11&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A12<>"", IFERROR(INDIRECT(PEOPLE!A12&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A13<>"", IFERROR(INDIRECT(PEOPLE!A13&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A14<>"", IFERROR(INDIRECT(PEOPLE!A14&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A15<>"", IFERROR(INDIRECT(PEOPLE!A15&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A16<>"", IFERROR(INDIRECT(PEOPLE!A16&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A17<>"", IFERROR(INDIRECT(PEOPLE!A17&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A18<>"", IFERROR(INDIRECT(PEOPLE!A18&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A19<>"", IFERROR(INDIRECT(PEOPLE!A19&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A20<>"", IFERROR(INDIRECT(PEOPLE!A20&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A21<>"", IFERROR(INDIRECT(PEOPLE!A21&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A22<>"", IFERROR(INDIRECT(PEOPLE!A22&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A23<>"", IFERROR(INDIRECT(PEOPLE!A23&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A24<>"", IFERROR(INDIRECT(PEOPLE!A24&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A25<>"", IFERROR(INDIRECT(PEOPLE!A25&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A26<>"", IFERROR(INDIRECT(PEOPLE!A26&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A27<>"", IFERROR(INDIRECT(PEOPLE!A27&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A28<>"", IFERROR(INDIRECT(PEOPLE!A28&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A29<>"", IFERROR(INDIRECT(PEOPLE!A29&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A30<>"", IFERROR(INDIRECT(PEOPLE!A30&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A31<>"", IFERROR(INDIRECT(PEOPLE!A31&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A32<>"", IFERROR(INDIRECT(PEOPLE!A32&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A33<>"", IFERROR(INDIRECT(PEOPLE!A33&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A34<>"", IFERROR(INDIRECT(PEOPLE!A34&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A35<>"", IFERROR(INDIRECT(PEOPLE!A35&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A36<>"", IFERROR(INDIRECT(PEOPLE!A36&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A37<>"", IFERROR(INDIRECT(PEOPLE!A37&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A38<>"", IFERROR(INDIRECT(PEOPLE!A38&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A39<>"", IFERROR(INDIRECT(PEOPLE!A39&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A40<>"", IFERROR(INDIRECT(PEOPLE!A40&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A41<>"", IFERROR(INDIRECT(PEOPLE!A41&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A42<>"", IFERROR(INDIRECT(PEOPLE!A42&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A43<>"", IFERROR(INDIRECT(PEOPLE!A43&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A44<>"", IFERROR(INDIRECT(PEOPLE!A44&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A45<>"", IFERROR(INDIRECT(PEOPLE!A45&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A46<>"", IFERROR(INDIRECT(PEOPLE!A46&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A47<>"", IFERROR(INDIRECT(PEOPLE!A47&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A48<>"", IFERROR(INDIRECT(PEOPLE!A48&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A49<>"", IFERROR(INDIRECT(PEOPLE!A49&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A50<>"", IFERROR(INDIRECT(PEOPLE!A50&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A51<>"", IFERROR(INDIRECT(PEOPLE!A51&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A52<>"", IFERROR(INDIRECT(PEOPLE!A52&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A53<>"", IFERROR(INDIRECT(PEOPLE!A53&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A54<>"", IFERROR(INDIRECT(PEOPLE!A54&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A55<>"", IFERROR(INDIRECT(PEOPLE!A55&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A56<>"", IFERROR(INDIRECT(PEOPLE!A56&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A57<>"", IFERROR(INDIRECT(PEOPLE!A57&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A58<>"", IFERROR(INDIRECT(PEOPLE!A58&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A59<>"", IFERROR(INDIRECT(PEOPLE!A59&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A60<>"", IFERROR(INDIRECT(PEOPLE!A60&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A61<>"", IFERROR(INDIRECT(PEOPLE!A61&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A62<>"", IFERROR(INDIRECT(PEOPLE!A62&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A63<>"", IFERROR(INDIRECT(PEOPLE!A63&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A64<>"", IFERROR(INDIRECT(PEOPLE!A64&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A65<>"", IFERROR(INDIRECT(PEOPLE!A65&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A66<>"", IFERROR(INDIRECT(PEOPLE!A66&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A67<>"", IFERROR(INDIRECT(PEOPLE!A67&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A68<>"", IFERROR(INDIRECT(PEOPLE!A68&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A69<>"", IFERROR(INDIRECT(PEOPLE!A69&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A70<>"", IFERROR(INDIRECT(PEOPLE!A70&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A71<>"", IFERROR(INDIRECT(PEOPLE!A71&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A72<>"", IFERROR(INDIRECT(PEOPLE!A72&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A73<>"", IFERROR(INDIRECT(PEOPLE!A73&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A74<>"", IFERROR(INDIRECT(PEOPLE!A74&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A75<>"", IFERROR(INDIRECT(PEOPLE!A75&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A76<>"", IFERROR(INDIRECT(PEOPLE!A76&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A77<>"", IFERROR(INDIRECT(PEOPLE!A77&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A78<>"", IFERROR(INDIRECT(PEOPLE!A78&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A79<>"", IFERROR(INDIRECT(PEOPLE!A79&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A80<>"", IFERROR(INDIRECT(PEOPLE!A80&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A81<>"", IFERROR(INDIRECT(PEOPLE!A81&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A82<>"", IFERROR(INDIRECT(PEOPLE!A82&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A83<>"", IFERROR(INDIRECT(PEOPLE!A83&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A84<>"", IFERROR(INDIRECT(PEOPLE!A84&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A85<>"", IFERROR(INDIRECT(PEOPLE!A85&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A86<>"", IFERROR(INDIRECT(PEOPLE!A86&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A87<>"", IFERROR(INDIRECT(PEOPLE!A87&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A88<>"", IFERROR(INDIRECT(PEOPLE!A88&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A89<>"", IFERROR(INDIRECT(PEOPLE!A89&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A90<>"", IFERROR(INDIRECT(PEOPLE!A90&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A91<>"", IFERROR(INDIRECT(PEOPLE!A91&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A92<>"", IFERROR(INDIRECT(PEOPLE!A92&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A93<>"", IFERROR(INDIRECT(PEOPLE!A93&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A94<>"", IFERROR(INDIRECT(PEOPLE!A94&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A95<>"", IFERROR(INDIRECT(PEOPLE!A95&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A96<>"", IFERROR(INDIRECT(PEOPLE!A96&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A97<>"", IFERROR(INDIRECT(PEOPLE!A97&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A98<>"", IFERROR(INDIRECT(PEOPLE!A98&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A99<>"", IFERROR(INDIRECT(PEOPLE!A99&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A100<>"", IFERROR(INDIRECT(PEOPLE!A100&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A101<>"", IFERROR(INDIRECT(PEOPLE!A101&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A102<>"", IFERROR(INDIRECT(PEOPLE!A102&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A103<>"", IFERROR(INDIRECT(PEOPLE!A103&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A104<>"", IFERROR(INDIRECT(PEOPLE!A104&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A105<>"", IFERROR(INDIRECT(PEOPLE!A105&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A106<>"", IFERROR(INDIRECT(PEOPLE!A106&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A107<>"", IFERROR(INDIRECT(PEOPLE!A107&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A108<>"", IFERROR(INDIRECT(PEOPLE!A108&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A109<>"", IFERROR(INDIRECT(PEOPLE!A109&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A110<>"", IFERROR(INDIRECT(PEOPLE!A110&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A111<>"", IFERROR(INDIRECT(PEOPLE!A111&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A112<>"", IFERROR(INDIRECT(PEOPLE!A112&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A113<>"", IFERROR(INDIRECT(PEOPLE!A113&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A114<>"", IFERROR(INDIRECT(PEOPLE!A114&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A115<>"", IFERROR(INDIRECT(PEOPLE!A115&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A116<>"", IFERROR(INDIRECT(PEOPLE!A116&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A117<>"", IFERROR(INDIRECT(PEOPLE!A117&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A118<>"", IFERROR(INDIRECT(PEOPLE!A118&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A119<>"", IFERROR(INDIRECT(PEOPLE!A119&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A120<>"", IFERROR(INDIRECT(PEOPLE!A120&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A121<>"", IFERROR(INDIRECT(PEOPLE!A121&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A122<>"", IFERROR(INDIRECT(PEOPLE!A122&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A123<>"", IFERROR(INDIRECT(PEOPLE!A123&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A124<>"", IFERROR(INDIRECT(PEOPLE!A124&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A125<>"", IFERROR(INDIRECT(PEOPLE!A125&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A126<>"", IFERROR(INDIRECT(PEOPLE!A126&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A127<>"", IFERROR(INDIRECT(PEOPLE!A127&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A128<>"", IFERROR(INDIRECT(PEOPLE!A128&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A129<>"", IFERROR(INDIRECT(PEOPLE!A129&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A130<>"", IFERROR(INDIRECT(PEOPLE!A130&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A131<>"", IFERROR(INDIRECT(PEOPLE!A131&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A132<>"", IFERROR(INDIRECT(PEOPLE!A132&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A133<>"", IFERROR(INDIRECT(PEOPLE!A133&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A134<>"", IFERROR(INDIRECT(PEOPLE!A134&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A135<>"", IFERROR(INDIRECT(PEOPLE!A135&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A136<>"", IFERROR(INDIRECT(PEOPLE!A136&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A137<>"", IFERROR(INDIRECT(PEOPLE!A137&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A138<>"", IFERROR(INDIRECT(PEOPLE!A138&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A139<>"", IFERROR(INDIRECT(PEOPLE!A139&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A140<>"", IFERROR(INDIRECT(PEOPLE!A140&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A141<>"", IFERROR(INDIRECT(PEOPLE!A141&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A142<>"", IFERROR(INDIRECT(PEOPLE!A142&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A143<>"", IFERROR(INDIRECT(PEOPLE!A143&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A144<>"", IFERROR(INDIRECT(PEOPLE!A144&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A145<>"", IFERROR(INDIRECT(PEOPLE!A145&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A146<>"", IFERROR(INDIRECT(PEOPLE!A146&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A147<>"", IFERROR(INDIRECT(PEOPLE!A147&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A148<>"", IFERROR(INDIRECT(PEOPLE!A148&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A149<>"", IFERROR(INDIRECT(PEOPLE!A149&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A150<>"", IFERROR(INDIRECT(PEOPLE!A150&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A151<>"", IFERROR(INDIRECT(PEOPLE!A151&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A152<>"", IFERROR(INDIRECT(PEOPLE!A152&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A153<>"", IFERROR(INDIRECT(PEOPLE!A153&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A154<>"", IFERROR(INDIRECT(PEOPLE!A154&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A155<>"", IFERROR(INDIRECT(PEOPLE!A155&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A156<>"", IFERROR(INDIRECT(PEOPLE!A156&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A157<>"", IFERROR(INDIRECT(PEOPLE!A157&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A158<>"", IFERROR(INDIRECT(PEOPLE!A158&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A159<>"", IFERROR(INDIRECT(PEOPLE!A159&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A160<>"", IFERROR(INDIRECT(PEOPLE!A160&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A161<>"", IFERROR(INDIRECT(PEOPLE!A161&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A162<>"", IFERROR(INDIRECT(PEOPLE!A162&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A163<>"", IFERROR(INDIRECT(PEOPLE!A163&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A164<>"", IFERROR(INDIRECT(PEOPLE!A164&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A165<>"", IFERROR(INDIRECT(PEOPLE!A165&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A166<>"", IFERROR(INDIRECT(PEOPLE!A166&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A167<>"", IFERROR(INDIRECT(PEOPLE!A167&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A168<>"", IFERROR(INDIRECT(PEOPLE!A168&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A169<>"", IFERROR(INDIRECT(PEOPLE!A169&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A170<>"", IFERROR(INDIRECT(PEOPLE!A170&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A171<>"", IFERROR(INDIRECT(PEOPLE!A171&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A172<>"", IFERROR(INDIRECT(PEOPLE!A172&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A173<>"", IFERROR(INDIRECT(PEOPLE!A173&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A174<>"", IFERROR(INDIRECT(PEOPLE!A174&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A175<>"", IFERROR(INDIRECT(PEOPLE!A175&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A176<>"", IFERROR(INDIRECT(PEOPLE!A176&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A177<>"", IFERROR(INDIRECT(PEOPLE!A177&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A178<>"", IFERROR(INDIRECT(PEOPLE!A178&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A179<>"", IFERROR(INDIRECT(PEOPLE!A179&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A180<>"", IFERROR(INDIRECT(PEOPLE!A180&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A181<>"", IFERROR(INDIRECT(PEOPLE!A181&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A182<>"", IFERROR(INDIRECT(PEOPLE!A182&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A183<>"", IFERROR(INDIRECT(PEOPLE!A183&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A184<>"", IFERROR(INDIRECT(PEOPLE!A184&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A185<>"", IFERROR(INDIRECT(PEOPLE!A185&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A186<>"", IFERROR(INDIRECT(PEOPLE!A186&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A187<>"", IFERROR(INDIRECT(PEOPLE!A187&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A188<>"", IFERROR(INDIRECT(PEOPLE!A188&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A189<>"", IFERROR(INDIRECT(PEOPLE!A189&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A190<>"", IFERROR(INDIRECT(PEOPLE!A190&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A191<>"", IFERROR(INDIRECT(PEOPLE!A191&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A192<>"", IFERROR(INDIRECT(PEOPLE!A192&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A193<>"", IFERROR(INDIRECT(PEOPLE!A193&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A194<>"", IFERROR(INDIRECT(PEOPLE!A194&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A195<>"", IFERROR(INDIRECT(PEOPLE!A195&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A196<>"", IFERROR(INDIRECT(PEOPLE!A196&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A197<>"", IFERROR(INDIRECT(PEOPLE!A197&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A198<>"", IFERROR(INDIRECT(PEOPLE!A198&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A199<>"", IFERROR(INDIRECT(PEOPLE!A199&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A200<>"", IFERROR(INDIRECT(PEOPLE!A200&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A201<>"", IFERROR(INDIRECT(PEOPLE!A201&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A202<>"", IFERROR(INDIRECT(PEOPLE!A202&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A203<>"", IFERROR(INDIRECT(PEOPLE!A203&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A204<>"", IFERROR(INDIRECT(PEOPLE!A204&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A205<>"", IFERROR(INDIRECT(PEOPLE!A205&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A206<>"", IFERROR(INDIRECT(PEOPLE!A206&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A207<>"", IFERROR(INDIRECT(PEOPLE!A207&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A208<>"", IFERROR(INDIRECT(PEOPLE!A208&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A209<>"", IFERROR(INDIRECT(PEOPLE!A209&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A210<>"", IFERROR(INDIRECT(PEOPLE!A210&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A211<>"", IFERROR(INDIRECT(PEOPLE!A211&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A212<>"", IFERROR(INDIRECT(PEOPLE!A212&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A213<>"", IFERROR(INDIRECT(PEOPLE!A213&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A214<>"", IFERROR(INDIRECT(PEOPLE!A214&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A215<>"", IFERROR(INDIRECT(PEOPLE!A215&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A216<>"", IFERROR(INDIRECT(PEOPLE!A216&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A217<>"", IFERROR(INDIRECT(PEOPLE!A217&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A218<>"", IFERROR(INDIRECT(PEOPLE!A218&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A219<>"", IFERROR(INDIRECT(PEOPLE!A219&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A220<>"", IFERROR(INDIRECT(PEOPLE!A220&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A221<>"", IFERROR(INDIRECT(PEOPLE!A221&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A222<>"", IFERROR(INDIRECT(PEOPLE!A222&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A223<>"", IFERROR(INDIRECT(PEOPLE!A223&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A224<>"", IFERROR(INDIRECT(PEOPLE!A224&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A225<>"", IFERROR(INDIRECT(PEOPLE!A225&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A226<>"", IFERROR(INDIRECT(PEOPLE!A226&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A227<>"", IFERROR(INDIRECT(PEOPLE!A227&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A228<>"", IFERROR(INDIRECT(PEOPLE!A228&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A229<>"", IFERROR(INDIRECT(PEOPLE!A229&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A230<>"", IFERROR(INDIRECT(PEOPLE!A230&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A231<>"", IFERROR(INDIRECT(PEOPLE!A231&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A232<>"", IFERROR(INDIRECT(PEOPLE!A232&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A233<>"", IFERROR(INDIRECT(PEOPLE!A233&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A234<>"", IFERROR(INDIRECT(PEOPLE!A234&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A235<>"", IFERROR(INDIRECT(PEOPLE!A235&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A236<>"", IFERROR(INDIRECT(PEOPLE!A236&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A237<>"", IFERROR(INDIRECT(PEOPLE!A237&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A238<>"", IFERROR(INDIRECT(PEOPLE!A238&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A239<>"", IFERROR(INDIRECT(PEOPLE!A239&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A240<>"", IFERROR(INDIRECT(PEOPLE!A240&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A241<>"", IFERROR(INDIRECT(PEOPLE!A241&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A242<>"", IFERROR(INDIRECT(PEOPLE!A242&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A243<>"", IFERROR(INDIRECT(PEOPLE!A243&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A244<>"", IFERROR(INDIRECT(PEOPLE!A244&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A245<>"", IFERROR(INDIRECT(PEOPLE!A245&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A246<>"", IFERROR(INDIRECT(PEOPLE!A246&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A247<>"", IFERROR(INDIRECT(PEOPLE!A247&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A248<>"", IFERROR(INDIRECT(PEOPLE!A248&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A249<>"", IFERROR(INDIRECT(PEOPLE!A249&"!A:F"), COLUMN(A:F)), COLUMN(A:F));
IF(PEOPLE!A250<>"", IFERROR(INDIRECT(PEOPLE!A250&"!A:F"), COLUMN(A:F)), COLUMN(A:F))}, COLUMN(B:F), 0)))

enter image description here

spreadsheet demo