I want populate cell by looking up data in another sheet

Question

I have an Excel workbook with 2 sheets.

I want to use Sheet 1 as the source to populate a column in Sheet 2.

Sheet 2, Column B has a list of resource names Sheet 1, contains many of the same names in Column A, and a list of managers in Column F I want to populate a separate column in Sheet 2 with the managers name from Sheet 1 based on matching the resource names.

I want to get a result of NOT FOUND if names don't match

I've tried IFERROR(VLOOKUP(B8,'SHEET 1',!$A$3:$AR:100,6,FALSE),"NOT FOUND"), but this formula always returns "NOT FOUND" even though there are matches in Sheet 1

Answer 1

Looks like the ranges in your vlookup are a little wonky, e.g., 'SHEET 1',!$A$3:$AR:100

To be a little more straighforward, let's give you an index/match formula, so you can more easily specify the search array from the output array:

=IfError(Index(Sheets1!$B:$B,Match(Sheets2!B8,Sheets1!$A:$A,0)),"Not Found")

How this works is you Match() the cell on Sheets2! (B8 from your example formula) within the search array, Sheets1!$A:$A (yes, you can use a whole column which makes things a little easier). If nothing is matched, it throws an error (so if-error handles that); if it is found, then it will output (index) based on the output array, Sheets1!$B:$B.

You will want to fix the search array column and output array column to fit your scenario.