CODEWITHSUNDEEP
excelgoogle-sheetsvlookup
aabujamra
aabujamra

Reputation: 4636

Dynamically change the matrix in VLOOKUP

I have this sheet:

  A                B
1 15576907000170   
2 17469701000177
3 8402943000152
4 17469701000177

I want to set a dynamic function on column B to check if all the numbers in that row have already appeared in A. So it would look like this:

  A                B
1 15576907000170   OK  
2 17469701000177   OK
3 8402943000152    OK
4 17469701000177   NOT OK

The closest I got was with this one:

= if(isnumber(VLOOKUP(A4;A:A;1;FALSE))=TRUE;"NOT OK"; "OK")

However it doesn't work because it will always find the number in column A. In the VLOOKUP I should be able to select only rows above the one from the actual cell, in the example above it would be something like this:

= if(isnumber(VLOOKUP(A4;A:A(ROW(A4-1);1;FALSE))=TRUE;"NOT OK"; "OK")

However, this gives me a synthax error. Can anyone help on running this function?

Upvotes: 0

Views: 170

Answers (2)

TheMaster
TheMaster

Reputation: 50654

B1: 

=ARRAYFORMULA(IF(MATCH(A1:A4;A1:A4;0)=ROW(A1:A4);"OK";"NOT OK"))
  • MATCH to find the first ROW number
  • IF first ROW = Current Row,OK,else,NOT OK

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36945

Use following formula in B1 cell then drag and down as needed.

=IF(COUNTIF($A$1:$A1,A1)>1,"Not Ok","Ok")

enter image description here

Upvotes: 2

Related Questions