Need to lookup employee info in a workbook with 77 worksheets. The worksheets are named with a numeric account number from 100000 to 670000 with no real pattern to the numbering.
I have a list of employee names starting in A1 that I need to lookup the first name only across all of the worksheets and return the first match only.
The data on the worksheets is from B2:D20 and I need to return the match in column D. The names are arranged as first name then space then middle initial or last name.
I have this formula but need to modify it to lookup the first name only: (Lookup_sheets is the range of worksheet names)
=VLOOKUP(A1,INDIRECT("'"&INDEX(Lookup_sheets,MATCH(1,--(COUNTIF(INDIRECT("'"&Lookup_sheets&"'!$B$2:$D$20"),A1)>0),0))&"'!$B$2:$D$20"),3,FALSE)
I have a list of employee names starting in A1 that I need to lookup the first name only across all of the worksheets and return the first match only.
The data on the worksheets is from B2:D20 and I need to return the match in column D. The names are arranged as first name then space then middle initial or last name.
I have this formula but need to modify it to lookup the first name only: (Lookup_sheets is the range of worksheet names)
=VLOOKUP(A1,INDIRECT("'"&INDEX(Lookup_sheets,MATCH(1,--(COUNTIF(INDIRECT("'"&Lookup_sheets&"'!$B$2:$D$20"),A1)>0),0))&"'!$B$2:$D$20"),3,FALSE)
Last edited: