Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Three columns and returning a value | Excel Discussion (Misc queries) | |||
Matching & Returning values | Excel Discussion (Misc queries) | |||
only returning 1st matching entry in table, need to find next matc | Excel Worksheet Functions | |||
returning all matching values in column A that have the same value for columnB | Excel Worksheet Functions | |||
Returning a Value by Matching Two Columns of Data | Excel Worksheet Functions |