Vlookup & IF

Josephine

Hi team,

I am trying to refer to another sheet which has active and inactive employees on it, some are returned employees so their name is twice on the sheet. However I only want the Vlookup to refer to Active employees.

I've tried these options with no success -

=IF({HRMS Current & Ex Employees Range 1} = "Active", VLOOKUP([Full Name]@row, {HRMS Current Employees Range 2}, 19, false), 0)

=IF({HRMS Current & Ex Employees Range 1}@row = "Active", VLOOKUP([Full Name]@row, {HRMS Current Employees Range 2}, 19, false), 0)

=IF({HRMS Current & Ex Employees Range 1} = "Active", VLOOKUP([Full Name]@row, {HRMS Current Employees Range 2}, 19, false))

This formula works fine but doesn't always refer to the active employee -

=VLOOKUP([Full Name]@row, {HRMS Current Employees Range 2}, 19, false)

Any help appreciated!

Kelly Moore

Hey @Josephine

An INDEX/COLLECT will accomplish what you need. The range in this function combo will be single columns, not a table as used in VLookUp.

=INDEX(COLLECT({source sheet employee column (what was 19 in table)}, {source sheet employee column to match to (what was 1 in table)},[Full Name]@row,{source sheet status column}, "Active"),1)

Will this work for you?

Kelly

Kelly Moore

Hey @Josephine

An INDEX/COLLECT will accomplish what you need. The range in this function combo will be single columns, not a table as used in VLookUp.

=INDEX(COLLECT({source sheet employee column (what was 19 in table)}, {source sheet employee column to match to (what was 1 in table)},[Full Name]@row,{source sheet status column}, "Active"),1)

Will this work for you?

Kelly

Josephine

Thank you! worked perfectly and I now know about INDEX/COLLECT!

Kelly Moore

Great! The Index/Collect is a powerful function combo. Another formula to explore is INDEX/MATCH which is used when one has only one criteria to consider. Both Index/Match and Index/Collect may increase sheet performance over the VLookUp.

Kelly