IF with INDEX / MATCH

Mary Farmer

Hi. I use INDEX/MATCH regularly, but want to add an "IF" statement and am getting #UNPARSEABLE, and would like your help.

I'd like to pull the "F Name" from Sheet 2 into Sheet 1, when the "Info Level" in Sheet 2 = "Primary".

This formula returns #UNPARSEABLE:

=IF({Sheet 2 Info Level Range} = "Primary"), INDEX({Sheet 2 F Name Range}, MATCH([Customer #]@row, {Sheet 2 Customer # Range}, 0))

Thanks in advance for your help!

Mary

Paul Newcome

It sounds like you need an INDEX/COLLECT.

=INDEX(COLLECT({Name Range}, {Customer Range}, @cell = [Customer #]@row, {Level Range}, @cell = "Primary"), 1)

Paul Newcome

It sounds like you need an INDEX/COLLECT.

=INDEX(COLLECT({Name Range}, {Customer Range}, @cell = [Customer #]@row, {Level Range}, @cell = "Primary"), 1)

Mary Farmer

Oh my goodness!!! Wonderful! Amazing. Thank you!

Paul Newcome

Happy to help. 👍️

Maz Uddin

@Paul Newcome hi Paul hope you're well

i tried to imitate your formula but it returns as #invalid value

my formula:

=INDEX(COLLECT({Commercials Master sheet Range 1}, {Commercials Master sheet Range 3}, @cell = Building@row, {Commercials Master sheet Range 2}, @cell = "Y"), 1)

not sure what im doing wrong

can you please advise

Paul Newcome

@Maz Uddin What does this give you?

=COUNTIFS({Commercials Master sheet Range 3}, @cell = Building@row, {Commercials Master sheet Range 2}, @cell = "Y")