Index/match plus lookup

[jcroyle]

Greetings all,

I am trying to modify an excel spreadsheet. This spreadsheet is for inventory control. The user will use a hand scanner to input the data from a bar code (just a #). It must then search a colum and determin if that # is there. If it is it must place a return value in another colum, same row to indicate that the number was found.

Any help?

 

[Jonmo1]

Welcome to the board..

If I understand right, you just want a validation that the value exists in another column?
Would a TRUE/FALSE result be acceptable?

=ISNUMBER(MATCH(A1,D:D,0))


A1 = scanned number
D:D = column to search for the number.

Returns TRUE if the value in A1 is found in column D, FALSE if it is NOT.

Hope that Helps

 

[jcroyle]

That may work. How do I return the value to the other colum?

 

[Jonmo1]

That may work. How do I return the value to the other colum?

Return WHAT value? The value in A1?

Try

=IF(ISNUMBER(MATCH(A1,D:D,0)),A1,"")

 

[jcroyle]

The true or flase value have to be returned to a cell in the same row of the number that was found.

Let's say A: is the colum I want the true or false sent to. It must place that true or false in the same row as the number that was found in colum C:

so enter the number, Searches colum C: finds it in C:11 Output the true to A:11

 

[Jonmo1]

so enter the number, Searches colum C: finds it in C:11 Output the true to A:11

Lets break that out into steps

1. so enter the number - WHERE exactly do you enter the number?
2. searches column
3. finds it in C11
4. output true to A11


Say you enter the number in B1

In A1 and filled down to the last row that holds data in Column C
=IF($B$1=$C1,TRUE,"")