Index match or collect

Christine Menke

HI Experts,

I try to set up a formula for below table.

Blue Part is on another sheet, and I would like to show the correct awbif article is the same and "Name" is "Kerstin" in the yellow marked column "awb new sheet".

I tried a few versions with Index and Match or collect, but without success.

Would be great if you could help me here.

Thanks in advance

Paul Newcome

Try something like this...

=INDEX(COLLECT({Source Sheet AWB Column}, {Source Sheet Article Column}, @cell = [Article New Sheet]@row, {Source Sheet Name Column}, @cell = "Kerstin"), 1)

Paul Newcome

We would use an IFERROR for that.

=IFERROR(INDEX(COLLECT({Source Sheet AWB Column}, {Source Sheet Article Column}, @cell = [Article New Sheet]@row, {Source Sheet Name Column}, @cell = "Kerstin"), 1), "")

Paul Newcome

Try something like this...

=INDEX(COLLECT({Source Sheet AWB Column}, {Source Sheet Article Column}, @cell = [Article New Sheet]@row, {Source Sheet Name Column}, @cell = "Kerstin"), 1)

Christine Menke

Thanks a lot @Paul Newcome

it works great, one more question if you don't mind.

If no awb available for the search criteria I would like to have the cell empty instead of invalid value.

What do I need to add?

Thanks

Paul Newcome

We would use an IFERROR for that.

=IFERROR(INDEX(COLLECT({Source Sheet AWB Column}, {Source Sheet Article Column}, @cell = [Article New Sheet]@row, {Source Sheet Name Column}, @cell = "Kerstin"), 1), "")

Christine Menke

Thanks a lot it's working :-)

Paul Newcome

Happy to help. 👍️