Index & Match

  • Thread starter Thread starter Greg Malenky
  • Start date Start date
G

Greg Malenky

Excel 2000

I have question on the best way to do this?

I have 5 columns on a worksheet (sheet #1)

subclass category style UPC # price
1010000 hockey hh1 654125 $29.99
1010000 hockey hh2 654137 $19.99
1010000 hockey ep2 457213 $11.99
1020400 Skates bar200 704154 $129.99

there is approximately 1500 items in each column.

I need to do a index and match so our employees can find
the correct UPC # & style when a subclass & price is
entered.

each subclass does not have any item that is the same
price. however, I tried to do a vlookup, but the price
column screws things up.

I have been able to create a table with prices as column
headers and rows with subclass titles. Then I can do a
hlookup using this style. However, getting the
information into the table is a lot more work.

Any ideas from your great minds out there?

Thanks
 
One way using OFFSET and MATCH:

Assume your sample table is in Sheet1
in cols A - E, row1 downwards

Put in F2: =TRIM(A2&"_"&E2)
copy F2 down the col

In another sheet, say Sheet2
----------------------------------
Assume cols A & B will be used for input
of the subclass & price, from A2 down

and cols C & D are where the corresponding
style & UPC# are to be returned

Put in C2:
=OFFSET(Sheet1!$A$1,MATCH(TRIM($A2&"_"&$B2),Sheet1!$F:$F,0)-1,COLUMN()-1)

Copy across to D2, then copy down

----------------------
You can also include an IF(ISNA(...),< Alert Message >,(...)) construct
to give alert messages for cases of no match found, viz:

Try instead, in C2:
=IF(ISNA(OFFSET(Sheet1!$A$1,MATCH(TRIM($A2&"_"&$B2),Sheet1!$F:$F,0)-1,COLUMN
()-1)),"No match
!",OFFSET(Sheet1!$A$1,MATCH(TRIM($A2&"_"&$B2),Sheet1!$F:$F,0)-1,COLUMN()-1))
 
Back
Top