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

#### jayd77

##### New Member
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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

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

jayd77 said:
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

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.

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

jayd77 said:
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?

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

Replies
7
Views
240
Replies
5
Views
160
Replies
2
Views
102
Replies
2
Views
107
Replies
5
Views
189

1,202,983
Messages
6,052,905
Members
444,611
Latest member
ggwpnore

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back