Formula Help

Caitlin R.

If I want to compare column 2 of Sheet D to column 2 in Sheet F, and return the value from column 1 in Sheet F to column 1 in Sheet D if 2D and 2F are equal, and not return anything if they are not equal, how would I put that in a formula? I think it's a vlookup but my formula didn't work.

Danielle Arteaga

Gotta start somewhere, right? :-)

I think you'd probably be good with an INDEX / MATCH formula.

=IFERROR(INDEX({Sheet F Column 2}, MATCH([Column2]@row, {Sheet F Column 2}, 0)), "")

Two important notes:
1. Make sure your the column type where you are placing the formula is set to text/number.

2. Make sure you use the IFERROR wrapper so that you get a "blank" when the value in Column 2 on Sheet D does not match a value from Column 2 on Sheet F.

Danielle Arteaga

Hi, @Caitlin R. How you do this depends a lot on what the values are in Column 2 of Sheet D and Sheet F. Are they numeric? Text? Are they unique, or can they repeat.

One simple way might be to use IF/MATCH. For example, let's say you have Sheet D and Sheet F set up like this:

On Sheet D, in the Primary Column, place this formula:
=IFERROR(IF(MATCH([Column2]@row, {Sheet F Column 2}, 0) > 1, [Column2]@row), "")

This returns a value only when Column 2 on Sheet D matches Column 2 on Sheet F.

BUT, if your column has duplicate values, this may not work. In that instance, you may need a version of IF/INDEX/MATCH or INDEX/COLLECT, but how that would work depends on the data / column types you have.

Caitlin R.

Thanks @Danielle Arteaga ! That got me something, which is more than what I had :)

2D is a 7-digit numerical value that may repeat in Sheet D but not in Sheet F, which is what I want Sheet D to compare to. (2F is also a 7-digit numerical value) If they are the same I want 1D to return the numerical value in 1F. All numbers.

However, the return is saying #invalid column value

Danielle Arteaga

Gotta start somewhere, right? :-)

I think you'd probably be good with an INDEX / MATCH formula.

=IFERROR(INDEX({Sheet F Column 2}, MATCH([Column2]@row, {Sheet F Column 2}, 0)), "")

Two important notes:
1. Make sure your the column type where you are placing the formula is set to text/number.

2. Make sure you use the IFERROR wrapper so that you get a "blank" when the value in Column 2 on Sheet D does not match a value from Column 2 on Sheet F.

Caitlin R.

Thank you! I switched out the first Sheet F Column 2 for Sheet F Column 1, but it is doing what I want it to now. Thank you!!!