Quote Originally Posted by Toadstool View Post
You can't have a single INDEX and/or MATCH using multiple ranges so you'll have to search each in turn.

I've put tables 1,3 and 4 on one sheet just to demonstrate (so you'll need to add sheet names to the arrays) and I'm showing Style Nos and Price in different columns just for fun.

The IFERROR searching tables 1 and 2 will fall into the next search if no match is found, otherwise it returns the first Pice. If the Style number in table 2 isn't on tables 1, 3 or 4 then you'll get a #N/A error.

A B C D E F G H I J K L
1 TABLE 1 WITH PRICES:
2 Style No. Suppl. Style brand Color Sex M L XL Quantity/Pcs Unit price Amount
3 14406146 MR-1 Mo light navy men 29 43 29 101 $2.50 $976.67
4 2222222 MR-1 Mo black men 29 43 29 101 $4.22 $976.67
5
6 TABLE 3 WITH PRICES:
7 Style No. Suppl. Style brand Color Sex Quantity/Pcs Unit price Amount
8 14406147 16203 Mo light navy men 101 $1.55 $1,139.28
9 14406147 16203 Mo black men 101 $1.55 $1,139.28
10 14406147 16203 Mo dark beige men 101 $1.55 $1,139.28
11
12 TABLE 4 WITH PRICES:
13 BD LD Style No. Suppl. Style brand Color Sex M L Quantity/Pcs Unit price Amount
14 33 22 14406148 3015 Mo navy men 29 43 101 $3.33 $1,374.61
15 22 33 14406148 3015 Mo black men 29 43 101 $3.33 $1,374.61
16
17 TABLE 2 WITH SHIPPED QUANTITIES:
18
19 Carton Total Value Shipped Style No. Extracted Price
20
21 1 1 14406146 2.5
22 5 1 2222222 4.22
23 9 1 14406147 1.55
24 1 1 14406148 3.33
25 5 1 3333333 #N/A
Sheet1 (2)

Worksheet Formulas
Cell Formula
E21 =IFERROR(INDEX($J$3:$J$4,MATCH(D21,$A$3:$A$4,0)),IFERROR(INDEX($H$8:$H$10,MATCH(D21,$B$8:$B$10,0)),INDEX($K$14:$K$15,MATCH(D21,$C$14:$C$15,0))))


thanks a ton!!!!!!