VLOOKUP

S

Scott Hamrick

I have a staffing program generating data in the following format. using 3
criteria shift, position (ie B61), secondary position (ie BC or Officer).
this list will be approx 300 records long. I have a second spreadsheet that
lists each position and needs to reach into this sheet and find the name of
the person that matches each of the 3 criteria. and i need help making the
formula.
Person Item (one column)
GARDEN, WAYNE J. A Shift - B61 - BC
BANKS, BARON W. A Shift - B66 - BC
MCADAMS, JIM A Shift - B71 - BC
SANTOS, MATTHEW D. A Shift - E61 - Engine Driver
FUHRMANN, NORBERT E. A Shift - E61 - Officer
NIELSON, JENNETT S. A Shift - L61 - Ladder Driver

here is an example of the second spreadsheet A-SHIFT B-SHIFT C-SHIFT
Engine 60
Officer name name name
Driver name name name
3rd name name name
4th name name name
Medic 60
PM name name name
RD name name name
Battalion 61
Batt. Chief name name name
Engine 61
Officer name name name
Driver name name name
3rd name name name
4th name name name
 
K

ker_01

Scott-

Since no-one else has taken a shot at this yet, I'll do my best.
My first suggestion is to make a few small formatting changes on your main
output sheet. It appears that your "position" is the first initial of the
'group' (eg. Engine 60 = E60, Battalion 61= B61). If so, then I would add a
column (which you can hide later) and put that position code on each line of
your output table for convenience.

I'd also take your source table, and reverse the column order- I believe
Vlookup requires the matching column to be on the far left. There are ways
around this, but this is the easy way to get to a Vlookup.

In your output worksheet, concatenate the column header, your new position
indicator on that line, the title, and any dashes in between. Note that your
column headers are not the same case as the source data ("A-SHIFT" vs "A
Shift") and are also missing the dash, make your column headers match what is
actually in your data. Your concatenated cell should match the source cells
format exactly.

Once you have concatenated these three values in your destination sheet and
reversed your source column order, a simple vlookup should work as expected.

If the instructions above aren't specific enough to help you, post again
with where you got stuck.

HTH,
Keith
 
Top