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

tbrinton

New Member
Joined
Jul 30, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
Where are your bus numbers on the Busses by Bus sheet?
 

tbrinton

New Member
Joined
Jul 30, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Attachments

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
That's what it should do. If it's not you will need to post some data from both sheets, we cannot debug images.
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top