How to lookup a value from another column and display Row number?

1
votes

I'm setting up an excel file for the operators. They will scan the Barcode from the product and it will populate in one of the column of the excel sheet. I have tried using LOOKUP formula in excel but it doesn't seem to be working right.

COL A : Data from Database COL B : Data from Scanner COL C: Row number

Find the value of Column B in Col A and populate the Row no. in Column C.

=LOOKUP(B5,A:A,ROW(A:A))

I have used this formula in every cell of the column C. enter image description here

The row number don't populate accurately.

I want to display GOOD OR BAD in ROW 2. GOOD - IF the scanner reads 6 barcodes and BAD if less than 6 How to change color to RED if the value is greater than 0?

2
excelexcel-formula
How to change color to RED if the SUM value in Col D is greater than 0? – user7377353

2 Answers

1
votes

Search B column in A column and get row back. You could use:

Note:

  1. Do not forget to use IFNA in case of B does not included in A.

  2. In my opinion there is no need to target whole range.

    =IFNA(MATCH(B1,$A$1:$A$6,0)+ROW($A$1:$A$6)-1,"")

Results:

enter image description here

3
votes

=MATCH(B5,A:A,0) should give you the rownumber. And if you wish to obtain the cell's address: =ADDRESS(MATCH(B5,A:A,0),1,4,1)

If you are using combined cells then you definiately want to use absolute ranges like =MATCH(B5,$A$5:$A$10,0)