How to lookup a value in a column of data not in ascending o

[jayd77]

This is where I am at currently . =LOOKUP(A3,Friday,Shift)

I am looking up which shift a particular employee(A3) is working on Friday.

Friday = a column containing all the names of employees who are working Friday (50 rows)

Shift = a column containing all shifts being worked (50 rows)

My current formula (=LOOKUP(A3,Friday,Shift)) returns N/A if the data is not in ascending order

 

[Ron Morris]

Re: How to lookup a value in a column of data not in ascendi

Hi,

Try Vlookup.

To make things really easy, hightlight both column Friday and shift (assuming that they are side by side) and give it a name in the namebox (Upper left hand corner, right above cell A1). Call it FRIDAY.

=VLOOKUP(A3,FRIDAY,2)

this will lookup the employee name in Friday (must be the far left column) and return the employees' shift, which is what the 2 represents, the second column.

Suppose you had something like ths for the whole week. Mon - Fri. Counting the employees' name, that would be a total of six columns. Highlight the whole six columns and 50 rows and call it SHIFTS.

For Monday =VLOOKUP(A3,SHIFTS,2)
For Tuesday = VLOOKUP(A3,SHIFTS,3)

and so on.

Hope this helps.

Ron

 

[Brian from Maui]

Re: How to lookup a value in a column of data not in ascendi

This is where I am at currently . =LOOKUP(A3,Friday,Shift)

I am looking up which shift a particular employee(A3) is working on Friday.

Friday = a column containing all the names of employees who are working Friday (50 rows)

Shift = a column containing all shifts being worked (50 rows)

My current formula (=LOOKUP(A3,Friday,Shift)) returns N/A if the data is not in ascending order

Use Vlookup and use 0 (false) as the 4th arguement

 

[jayd77]

Re: How to lookup a value in a column of data not in ascendi

Appreciate the input but don't believe that will work in my situation

I have a list of bus routes going down my far left column. The day's of the week are listed across the top row. The schedule is done by assigning a different driver to each route everyday. So under each day the driver's names are listed in a somewhat random order going down the column for that day.

On a different worksheet I am keeping track of their hours. This is where I am using the lookup formula.

 

[Brian from Maui]

Re: How to lookup a value in a column of data not in ascendi

Appreciate the input but don't believe that will work in my situation

I have a list of bus routes going down my far left column. The day's of the week are listed across the top row. The schedule is done by assigning a different driver to each route everyday. So under each day the driver's names are listed in a somewhat random order going down the column for that day.

On a different worksheet I am keeping track of their hours. This is where I am using the lookup formula.

How about posting some sample data and your expected results. Where are the hours listed that your trying to get?

 

[Application Solutions]

Re: How to lookup a value in a column of data not in ascendi

You can run an Index/Match formula for a range in no particular order to get the results you need.

Add a new worksheet with a list in column A with all of your employees names.

Column B will be Monday, C Tuesday, D Wednesday, E Thursday, F Friday.

in cell b2 write this formula: =Index(Sheet1!$A$2:$F$51,Match($A2,Sheet1!$A$2:$F$51,0),2)

This formula assumes the following:

1) your list of bus routes is on the sheet titled Sheet1
2) your bus routes are in Column A and start at row 2 and end at 51

Now you drag the cell b2 across to f2. This transfers the formula across the week for the first employee. In cell c2 you will need to change the last part of the formula from a 2 to a 3. In cell d2 change to a 4, e2 change to a 5 and in f2 to a 6. This tells the formula what range to lookup to search for the employee name. Make sure the names are spelled the same though on the worksheet with the busroutes and on the new sheet you just created.

Now you drag selection B2:F2 down to row 51. See what happens!

This should create a list for each employee that shows what shift every employee has for each day of the week.

If you need me to, I could make an example sheet to show you this very powerful formula and email it to you

Adam