INDEX, MATCH, IF Formula

brianne111081

I am trying to pull a value from another sheet if the cell matches another cell and IF it is not checked off in the other sheet:

If the ABV on sheet 1 matches the course in sheet 2, I'd like to pull the "EPM" from sheet 2 into sheet 1, but ONLY if the "Archived" column on sheet 2 is not checked.... (I want EPM on sheet 1 to pull "Shelly")

Sheet 1:

Sheet 2:

Thank you!

Ryan Sides

@brianne111081

Hi Brianne,

See if this one works for you...

=IF(IFERROR(INDEX({Archived}, MATCH([ABV.]@row, {Course}, 0)), "Course Not Found") = "Course Not Found", "Course Not Found", IF(INDEX({Archived}, MATCH([ABV.]@row, {Course}, 0)) = 0, IF(INDEX({EPM}, MATCH([ABV.]@row, {Course}, 0)) = "", "No EPM", INDEX({EPM}, MATCH([ABV.]@row, {Course}, 0))), "Course Archived"))

I built it so it would tell you what's going on "No EPM", "Course Not Found", "Course Archived".

Sheet1:

Sheet2:

Please let me know if that works like you want it to!

-Ryan

Maximus

@brianne111081 ,

You can find more information in the article by @Brett Wyrick.

Alternatively, if you are searching for values from Sheet 1 in Sheet 2 across the entire "Course" column, you can use VLOOKUP function instead of INDEX and MATCH combination:

=IF(ISERROR(VLOOKUP([ABV.]@row, {Sheet 2 Range 1}, 4, false)=FALSE,IF(VLOOKUP([ABV.]@row, {Sheet 2 Range 1}, n, false)=true, VLOOKUP([ABV.]@row, {Sheet 2 Range 1}, m, false),""),"").

Here {Sheet 2 Range 1} represents your [Range to look for Identifier], and "m" and "n" are your "EPM" and "Archived" column order numbers with respect to the "Course" field.

Ryan Sides

@Maximus Great point! One caution with VLOOKUP is that if the column sequence changes (someone adds an additional column in the middle), the formula breaks. I was a VLOOKUP fan forever then, my columns were moved and all of my formulas were hosed. I then became an INDEX/MATCH guy and haven't looked back. :)