Vlookup Help

[jsyowler]

Trying to make multiple lists based on the first character of a maste
list onto seperate sheets. I.E. I have a master list in sheet 1.
would like to make a list for each letter A-Z on corresponding sheets.
have 4500 items in the master list. Cannot figure out how to use th
Vlookup and Left function together as well as returning multiple item
between seperate sheets. Thank for all the help in advance

 

[Don Guillett]

Using vlookup if NOT sorted you must use the false or 0 parameter.
Look in the help index. Can't you just sort???

 

[zvkmpw]

Trying to make multiple lists based on the first character of a master
list onto seperate sheets.  I.E. I have a master list in sheet 1.  I
would like to make a list for each letter A-Z on corresponding sheets. I
have 4500 items in the master list.

Here's one way with Excel 2003, but it doesn't use VLookup.

In my example, row 1 in sheet 1 is a header row, and the data starts
in row 2.

Also, sheet 1 has data in rows A:F.

Each "letter" sheet uses column A as a helper column.

Set up each "letter" sheet by typing its letter in its cell A1.

Next enter the following formulas in the "A" sheet:

In cell A2:
=IF(LEFT('sheet 1'!A2,1)=$A$1,MAX(A$1:A1)+1,"")

In cell B2:
=IF(ROW()-1>MAX($A:$A),"",
IF(OFFSET('sheet 1'!A$1,MATCH(ROW()-1,$A:$A,0)-1,0)="","",
OFFSET('sheet 1'!A$1,MATCH(ROW()-1,$A:$A,0)-1,0)))

Next copy B2 into C2:G2 (or however many columns you use).

Next select A2:G2 and copy down for the 4500 rows.

Next copy the rows 2:4500 and paste them into the same place in the
other "letter" sheets. You can do this with one paste by multi-
selecting the other sheets.

To avoid clutter, the helper column in each "letter" sheet can be
hidden.

Hope this helps getting started.