# Excel Loop Lookup [Closed]

-
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.

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