Lookup excluding value

[Anapfon]

Morning everyone!

I need to report on how many transactions each member of my team processed within a month but the process starts with a computer generated ID (so, it's an automatic upload into SAP that someone in my team picks up and populate everything we need to), which I need to exclude from my graphs...

I've created a list of unique product # but when I VLOOKUP the person ID to show me who actioned the request, excel brings the system ID (since that was the first action). I can't exclude the system ID because I also use the same report to show the time taken to process everything. So, my question is:

How can I bring into my list of unique product # the ID of the person who actioned the request skipping the system ID (ANA1 in my example sheet), please? So it would be something like, "if the ID is ANA1 then jump to the next line and bring on that value".

Your help is much appreciated!

Cheers!

 

[Nebu]

Hi:

Your post is really confusing, can you elaborate a bit and workout a sample expected output manually.

Anyhow, I have constructed the following formula as per my understanding.

=INDEX($B$2:$B$15,MATCH(1,MMULT((D2=$A$2:$A$15)*($B$2:$B$15<>"ANA1"),1),0),1)

Thanks

 

[Anapfon]

Sorry about that, so, ultimately, I want a formula to bring the value from column B into column E, excluding the ID "ANA1". So, if I take the example of product 123456, I need a formula to ignore the ID ANA1 and bring the value "2ANA" into column E; and value "3ANA" into column E for product 659875. I hope this clarifies... Cheers!

 

[Anapfon]

Hi:

Your post is really confusing, can you elaborate a bit and workout a sample expected output manually.

Anyhow, I have constructed the following formula as per my understanding.

=INDEX($B$2:$B$15,MATCH(1,MMULT((D2=$A$2:$A$15)*($B$2:$B$15<>"ANA1"),1),0),1)

Thanks

Awesome! it worked! Thank you very much!!

 

[tomhouy]

Is the auto generated system ID always going to be "ANA1"? If so, you could possibly accomplish this with a Pivot Table. I've included an example to illustrate this.

Basically just:

- Drag "Object Value" and "User" into the Rows panel in that order.

- Under the Field Settings for each, turn off subtotals and set it to "Show Item Labels In Tabular Form"

- Under the Pivot Table Options, turn off the Grand Totals.

- Then under the label filters for the Pivot Table, just set it to Does Not Equal "ANA1".