Index match question

[Philippe de Vlaminck]

Hey all,

I have been looking for an answer to following match/lookup question:
I have a column with recurring products and one with a status:

Product A Ok Lookup /match column
Product B NOk
Product C Ok
Product D Ok
Product E NOK
Product A Ok
Product C NOK
Product B OK
...


And I would like to look through the column for a specific product and return an ok to the lookup column if it exists in the lookup range.

For instance row 2 product B is NOK however on the bottom row it is. Therefor it should return an OK.

Anyone help would be much appreciated.
Been looking at index match and index small formulas but no luck figuring it out yet

Kind regards,
Phil

 

[Gerald Higgins]

Consider a pivot table, which could be set up to return results like the below . . .

..............Count of NOK...Count of OK
Product A............................2
Product B..........1.................1
Product C..........1.................1
Product D............................1
Product E...........1.................

Alternatively, consider concatenating the product code and the status code, and using that combined field as a lookup.

 

[Marcílio_Lobão]

Philippe de Vlaminck, Good afternoon.

I did not fully understand your explanation.

Is your product name followed by a Ok or NOK at column A?

Please, use the Advanced button the design correctly your layout.

It will be easier and faster to help you conclusively.

 

[Philippe de Vlaminck]

Thx for the reply, they are all seperate columns. I would prefer not using a pivot table for solver use reasons.

The table looks as follows e.g.:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product A[/TD]
[TD]Ok[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]NOk[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]Ok[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]Ok[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD]Nok[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]Ok[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]Ok[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product E[/TD]
[TD]Ok[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If for a specific product e.g. Product B Column B is OK, it should return OK for all Product B rows.

Hope this clarifies it abit.

thx again.
Philippe

 

[Gerald Higgins]

OK, how about my second suggestion ?

 

[Philippe de Vlaminck]

Thx Gerald, I will give it a try, currently in meeting :/

Kind regards,
Philippe

 

[Philippe de Vlaminck]

Hey All

I have tried Geralds option but am unsure of what is meant by use it as lookup. The first column is variable due to changing products in storage.
As this column changes checks are performed to see whether a certain service remains possible.

I need to check for a product is if the service is possible at all on another row.

Kind regards,
Philippe

 

[Philippe de Vlaminck]

Fount it, not so elegant but it seems to work.
Added a column with =CONCATENATE([@[Future product]],[@STATUS])
Created a lookup table for the product names with Ok
Following formula in 3rd column: =IFERROR(VLOOKUP(IF([@[DEL RL]]<>"Ok",VLOOKUP(CONCATENATE([@[Future product]],"Ok"),[DEL RL Prod concatenate],1,FALSE),""),'Product service list'!$A$2:$B$117,COLUMN('Product service list'!$B$2),FALSE),"")

Thx gents. Cleanup service welcome to make it more lean

 

[Marcílio_Lobão]

Philippe de Vlaminck,

Take a look at it:

Please, tell us if it is what you're looking for.

I hope it helps.[TABLE="class: grid, width: 450, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"] A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Product A[/TD]
[TD="align: center"]OK[/TD]
[TD]=IF(COUNTIFS($A$1:$A$8,A1,$B$1:$B$8,"OK")>0, "OK","------ ------")[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Product B[/TD]
[TD="align: center"]NOK[/TD]
[TD="align: center"]Copy this formula down ........[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Product C[/TD]
[TD="align: center"]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Product A[/TD]
[TD="align: center"]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Product D[/TD]
[TD="align: center"]NOK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Product B[/TD]
[TD="align: center"]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Product C[/TD]
[TD="align: center"]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Product E[/TD]
[TD="align: center"]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD].............[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Philippe de Vlaminck]

Thx Marcilio, looks logical ill give it a shot

Kind regards,
Philippe