#invalid Column Value Error

Cemil Binlik

Hello Team,

I have a sheet with two columns, one is set as text (referred as NTP on the SS below) and the other one is set as date (NTP Received (A) on the SS below).

I am trying to apply below if formula on another to sheet on a column that is set as date but I get the #Invalid Column Value Error on the ones that are other than N/A.

Please see below SS and advise where I might be doing wrong.

Thank you in advance.

The original Sheet

The other sheet that I am trying to pull from below formula:

=IF(INDEX({4- Planning Range 3}, MATCH([Site Name]@row, {4- Planning Range 2}, 0)) = "Yes", {4- Planning Range 4}, "N/A")

Cemil Binlik

Planning Range 3 is the NTP Required column on the 1st SS.

Planning Range 2 is the Site Name column on the 1st SS.

Planning Range 4 is the NTP Received (A) column on the 1st SS

Paul Newcome

Instead of just outputting the range, use an INDEX/MATCH to pull in the appropriate date.

Cemil Binlik

I want to see the date if the NTP required section is Yes, otherwise I want to see blank there

Sameer Karkhanis

Assuming you Site Names are unique in the lookup sheet, you can use the formula below (Please make sure the formula column aka NTP Status in your case, has been defined as Date column)

=IFERROR(INDEX(COLLECT({NTP Received (A) Column Range}, {Site Name Column Range}, [Site Name]@row, {NTP Required Column Range}, "Yes"), 1), "") 

Cemil Binlik

@Sameer Karkhanis looks like it did the job, thank you. I've using join and collect for a long time already, but as I see, collect function has a lot more that it can do. Thanks

Sameer Karkhanis

Glad it worked, and yes there are always interesting ways on how the functions can be used. I too keep learning through questions and suggestions in this forum.