Matching and returning only one cell
 

I am trying to "true" up my inventroy and need to set up a function that will
allow me to look at what my warehouse has on hand Vs. what my system shows on
had. here is an example:

Table 1
SKU Number: Avail: ADJ: BOH:
20-11620-10402 1056 1056
20-11710-10410 17424 17424
20-11810-10420 21528 21528
20-11811-10423 7404 7404
20-11851-10423 29520 29520
20-11910-10430 30672 30672

Table 2
SKU Number: Avail: ADJ: BOH:
20-11620-10402 64 64
20-11710-10410 324 324
20-11720-10412 60 60
20-11810-10420 396 396
20-11810-35420 1080 1080
20-11910-10430 396 396
20-11910-20430 1080 1080

my question is these 2 tables are on the same sheet the first starts on a3
and the second starts at a20.

How can I search for the matching SKU Number off of table 1 on table 2 and
return the negitive of the number in the BOH field to the ADJ colom in table
2 but the positive number to table 1

Matching and returning only one cell
 

With Table 1 data starting in A4, use this formula in the ADJ column...
=IF(ISNUMBER(MATCH(A4,$A$21:$A$27,0)),VLOOKUP(A4,$ A$21:$D$27,4,0),0)

With Table 2 data starting in A21, use this formula in the ADJ column...
=IF(ISNUMBER(MATCH(A21,$A$4:$A$9,0)),-D21,0)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Matticoda"
wrote in message
I am trying to "true" up my inventroy and need to set up a function that will
allow me to look at what my warehouse has on hand Vs. what my system shows on
had. here is an example:

Table 1
SKU Number: Avail: ADJ: BOH:
20-11620-10402 1056 1056
20-11710-10410 17424 17424
20-11810-10420 21528 21528
20-11811-10423 7404 7404
20-11851-10423 29520 29520
20-11910-10430 30672 30672

Table 2
SKU Number: Avail: ADJ: BOH:
20-11620-10402 64 64
20-11710-10410 324 324
20-11720-10412 60 60
20-11810-10420 396 396
20-11810-35420 1080 1080
20-11910-10430 396 396
20-11910-20430 1080 1080

my question is these 2 tables are on the same sheet the first starts on a3
and the second starts at a20.

How can I search for the matching SKU Number off of table 1 on table 2 and
return the negitive of the number in the BOH field to the ADJ colom in table
2 but the positive number to table 1