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

[tbrinton]

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

 

[Fluff]

You should never use the sheet name for the sheet the formula is on, try it like

=INDEX('Long List'!$A$1:$A$272,MATCH(B2,'Long List'!$B$1:$B$272,0))

 

[tbrinton]

You should never use the sheet name for the sheet the formula is on, try it like

=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]

Where are your bus numbers on the Busses by Bus sheet?

 

[tbrinton]

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]

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]

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

 

[Fluff]

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]

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]

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