Aligning columns with IF Function

Question

Hi All, I'm struggling with this issue.

Essentially, I have two groups, 1 and 2, which have correlating scores. I need to match the score columns from the two groups (so the score for A in Group 1 (Cell B2) is next to the score for A in Group 2 (Cell F8).

I've narrowed it down to an IF function spread over a range of cells as you can see in the image, but I get a spill over of every false value. I just want one cell where the correct value is, as shown in the bottom table.

I would appreciate all help, thank you!

Answer 1

If you want the XLOOKUP formula to return blanks instead of zeros:

=XLOOKUP(A2,$E$2:$E$12,$F$2:$F$12,"")

Answer 2

Hi Jack

Try this formula in cell C2 and copy it down

=IFERROR(VLOOKUP(A2,$E$1:$F$12,2,0),"")

If Group 1 is not found in Group 2 table will show "blank"

I hope this helps you

Regards

Jeovany

Answer 3

In C2:

=XLOOKUP(A2,$E$2:$E$12,$F$2:$F$12,0)

Fill down to C12

Answer 4

Jeovany, this is amazing, thank you so much - No 0's too, that's a nice touch!

Answer 5

In C2:

=XLOOKUP(A2,$E$2:$E$12,$F$2:$F$12,0)

Fill down to C12

Thank you Hans, this worked like a treat!