Excel Loop Lookup [Closed]

Report
-
Posts
1854
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
December 4, 2020
-
I am trying to do a lookup on following sheet (4 columns Store, Part, K or R, New K or R):

Store Part K or R New K or R
1 ABC R
2 ABC K
3 ABC R
1 KLM K
2 KLM K
3 KLM R
1 QRS R
2 QRS R
3 QRS K
2 XYZ R
3 XYZ K

I would like to write a formula that will look at all rows that contain the same part number and look for a "K" in the K or R field. If it finds a K in any of the 3 stores, I want it to put a K in the New K or R cell for each store. If it does not find a K, I want it put a R in the cell for each store.

My reasoning logic: I am doing an inventory obsolescence study and I want to analyze each part in all stores where the part exists. If any of the stores has a K (Keep), I want it to put a K for all stores. If it does not, it should put an R (Return). The point is that if we sell it in any store then we want to keep the inventory in all stores.

1 reply

Posts
1854
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
December 4, 2020
136
InvDR, Good evening.

........A.........B........C..................D.............
1...Store...Part....K or R.....New K or R
2......1.......ABC.......R............=FORMULA
3......2.......ABC.......K............
4......3.......ABC.......R............
5......1.......KLM.......K............
6......2.......KLM.......K............
7......3.......KLM.......R............
8......1.......QRS.......R............
9......2.......QRS.......R............
10....3.......QRS.......K............
11....2.......XYZ.......R............
12....3.......XYZ.......K............

Try to do:

D2 --> =IF(COUNTIFS($B$2:$B$12,B3;$C$2:$C$12,"K")>0,"K","R")

Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!