# Looking up a value in the second column and returning the value into the first column

#### tbrinton

##### New Member
Good afternoon,

I have a spreadsheet with two different tabs. This is for school bus info for context. I am trying to have one tab sort by Route Number and the second tab use the same data to sort by Bus Number. I want both tabs to pull their data from a third tab, which would allows us to make changes on just the 3rd tab and have it automatically update tabs 1 and 2. I have the columns arranged on tab 3 by Route Number, Bus Number, and Driver Name. The first tab is an easy enough VLOOKUP command, but I cant seem to get the second tab to work, since VLOOKUP has trouble looking at a column that isn't the far left column. Is there a formula I can use where it pulls the second column for bus number and returns the route number in the first column?

Code for VLOOKUP in first tab: =IFERROR(VLOOKUP(A3,'Long List'!\$A\$1:\$C\$270,2,FALSE),"")

The tabs names are 'Busses by Route #', 'Busses by Bus #', and 'Long List'. I want both Busses tabs to pull their data from Long List.

I saw a similar thread that mentioned INDEX and MATCH. I tried this code out, but it doesnt work if the bus number moves on Long List.
=INDEX('Long List'!\$A\$1:\$A\$272,MATCH('Busses by Bus #'!\$B\$2,'Long List'!\$B\$1:\$B\$272,0))

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Fluff

##### MrExcel MVP, Moderator
You should never use the sheet name for the sheet the formula is on, try it like
Excel Formula:
``=INDEX('Long List'!\$A\$1:\$A\$272,MATCH(B2,'Long List'!\$B\$1:\$B\$272,0))``

#### tbrinton

##### New Member
You should never use the sheet name for the sheet the formula is on, try it like
Excel Formula:
``=INDEX('Long List'!\$A\$1:\$A\$272,MATCH(B2,'Long List'!\$B\$1:\$B\$272,0))``
It returns the value I currently have it set at, but if i go in and change the bus number to a different route, it returns an #NA. Is there anyway for it to update?

#### Fluff

##### MrExcel MVP, Moderator
Where are your bus numbers on the Busses by Bus sheet?

#### tbrinton

##### New Member

Where are your bus numbers on the Busses by Bus sheet?
They are in column B, following the same pattern as Busses by Route.
RTE, Bus #, Driver.

#### Fluff

##### MrExcel MVP, Moderator
In that case it should work, can you post some sample data of both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

#### tbrinton

##### New Member

Here's an example @Fluff of the layout of Busses by Bus

#### Attachments

• Busses by Bus example..png
13.6 KB · Views: 4

#### Fluff

##### MrExcel MVP, Moderator
It looks as though some of the values in col B are numbers & some are text. They should all be the same as they are on the master sheet.

#### tbrinton

##### New Member
It looks as though some of the values in col B are numbers & some are text. They should all be the same as they are on the master sheet.
Yes. For some reason, some of the bus numbers keep getting formatted as a date instead of a number. >_< thats a different headache. Im mainly trying to make it so if we update data on Long List, it will automatically update the same info here on Bus by Bus number. So if I, for example, change 401 from route C29 to C14, it should automatically update Column A to reflect C14 next to 401.

#### Fluff

##### MrExcel MVP, Moderator
That's what it should do. If it's not you will need to post some data from both sheets, we cannot debug images.

Replies
15
Views
372
Replies
0
Views
88
Replies
7
Views
87
Replies
3
Views
113
Replies
1
Views
46

1,147,962
Messages
5,744,057
Members
423,843
Latest member
alex2022

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