# INDEX question with multiple criteria (example provided)

#### ryan0000

##### New Member
Hello all,

I'm a longtime user of this forum, but this is the first thing specific enough that I needed to ask your guys' help.

I have an inventory report I've created that is referencing different weekly worksheets within the excel workbook, each labeled 20, 21, 23 etc. I've created a way to not use macros that has allowed me to do everything I need, except for this one part:

Here is the way my table is setup: I'm having it list from highest excess inv. value to lowest.

Week: [23]

G H I J
5 Manager | Product ID | Product Name | Excess Inventory Value
6 Joe Blow 11111 Name1 \$230,000
7 Jane Smith 11142 Different Name \$190,000
8 Jenny Cox 32544 Other Name \$170,000

Manager Formula for G6: =VLOOKUP(H6,INDIRECT(\$G\$2&"!"&"b:v"),21,0)

Product ID Formula for H6: =INDEX(INDIRECT(\$G\$2&"!"&"\$B\$2:\$B\$10000"),MATCH(1,INDEX((INDIRECT(\$G\$2&"!"&"\$U\$2:\$U\$10000")=LARGE(INDIRECT(\$G\$2&"!"&"\$U\$2:\$U\$10000"),ROWS(H\$5:H5)))*(COUNTIF(H\$5:H5,INDIRECT(\$G\$2&"!"&"\$B\$2:\$B\$10000"))=0),),0))

Product Name Formula for I6: =VLOOKUP(H6,INDIRECT(\$G\$2&"!"&"b:d"),3,0)

Excess Value Formula for J6 =VLOOKUP(H6,INDIRECT(\$G\$2&"!"&"b:u"),20,0)

All of the VLOOKUP formulas are easy, but what I need help with is the Product ID Formula (everything else flows from there). I want it to only list the Product IDs that are for a specific Manager. Right now everything is working perfectly, except I cannot figure out a way to get it to only list for 1 manager, instead of just listing all of them.

On the data worksheets, Column B is Product IDs, Column U is the Excess Inventory Level, and Column V is the Manager Name.

Thank you in advance for any help! I'm going nuts.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Replies
4
Views
386
Replies
6
Views
611
Replies
3
Views
311
Replies
3
Views
227
Replies
3
Views
175

1,195,905
Messages
6,012,229
Members
441,683
Latest member
XLGeezer

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

### Which adblocker are you using?

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

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