This is an odd one. Here is the formula.

=IFNA(IF(VLOOKUP(C255,'Task Data'!$A$4:$AT$3005,39,'Task Data'!$AM$4:$AM$3005)=1,"Non-Compliant","Compliant"),"Compliant")

The issue is when the function looks in 'Task Data' for what C255 is and there are two of them (in two different rows) it appears it only works if 1 is in the top row, i.e. it results and shows "Compliant". If the 1 is in the lower row, it still shows "Compliant" but it should be "Non-Compliant". It appears it is not seeing the second 1 in the second row. Hopefully this makes sense.

  • You mean to say that the value in C255 is available in both data ranges A4:AT3005 & AM4:AM3005 and you are trying to extract both ? – Rajesh S Jul 26 '18 at 9:15
  • I think your formula should written like this =IF(VLOOKUP(C255,AM4:AM3005,1,TRUE)=1, VLOOKUP(C255,A4:AT3005,39,TRUE), NA()) – Rajesh S Jul 26 '18 at 9:30

VLOOKUP has a fourth parameter, TRUE or FALSE.

You need to set it depending on if you look for an exact match (FALSE), or if you have sorted data and want to interpolate between the next best value (TRUE).
The default is TRUE, which is a common issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.