Index match question

Joined
Feb 16, 2016
Messages
21
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Well-known Member
Joined
Oct 7, 2013
Messages
733
Platform
  1. Windows
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.
 
Joined
Feb 16, 2016
Messages
21
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.:

Product AOk
Product BNOk
Product COk
Product AOk
Product DNok
Product BOk
Product COk
Product EOk

<tbody>
</tbody>


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
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

OK, how about my second suggestion ?
 
Joined
Feb 16, 2016
Messages
21

ADVERTISEMENT

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
 
Joined
Feb 16, 2016
Messages
21
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

Well-known Member
Joined
Oct 7, 2013
Messages
733
Platform
  1. Windows
Philippe de Vlaminck,

Take a look at it:

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

I hope it helps.
ABC
1Product AOK=IF(COUNTIFS($A$1:$A$8,A1,$B$1:$B$8,"OK")>0, "OK","------ ------")
2Product BNOKCopy this formula down ........
3Product COK
4Product AOK
5Product DNOK
6Product BOK
7Product COK
8Product EOK
.............

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,124
Messages
5,599,839
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top