Forum Discussion
Index Match problem
I am having a problem with what should be a simple solution. In the example below, I am trying to create an Index Match formula to look for one value in a table and pull in the Catalog ID. For example, if I look for PBC-1430, I need 2D4 to populate my cell:
| Catalog ID | Cust A Item #s | Cust B Item #s | Cust C Item #s | Cust D Item #s | Cust E Item #s | Cust F Item #s |
| A1 | 05BLL | 05BAA | 10000A | 1000AFA | BAT2000 | |
| 0B1 | 04BLL | 04BAA | 2000B | 2000AFA | BATC2000 | |
| C5 | 06B1L | 06B1A | PBC-1430 | |||
| 2D4 | 08D1LL | 08D1AA | PBC-1460 |
I originally tried using a vlookup, but that didn't work. Any assistant would be much appreciated.
- To remedy the possible occurrence of duplicates, I suggest this formula in Inventory!A16:
=IFERROR(INDEX('Original Catalog & Customers'!B$6:B$16,
SUMPRODUCT(MAX(ROW('Original Catalog & Customers'!C$6:I$16)*
('Original Catalog & Customers'!C$6:I$16=B16)))-5),
"No such")
10 Replies
Hi MeMacz1138 ,
That is like
=IFERROR(INDEX($A$2:$A$1000,MATCH(J4,$G$2:$G$1000,0)),"no such")
if the value you are looking for is in J4
SergeiBaklan Thank you for responding so quickly! Is there a way to do the lookup in all cells from B1 through G5? Rather than only looking in Column G?
MeMacz1138 , it depends on what you'd like to do. If multiple criteria, like return value from A if in G we have some1 AND in C some2, etc. - that's one situation. If pull value not from column A but another one that will be third parameter INDEX. Similar to VLOOKUP when you have columns number as parameter.
What is your case exactly? Perhaps just small sample to explain.
