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

T

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))
 
Sort by date Sort by votes
Fluff said:
That's what it should do. If it's not you will need to post some data from both sheets, we cannot debug images.
Click to expand...
Sorry for the delayed response. I had to wait to get home before I could download the addon. I am trying to use it, but my excel keeps freezing too
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Bus Route Listing.xlsx
ABCDEFGHIJK
1RTEBUS #DRIVER RTEBUS #DRIVER RTEBUS #DRIVER
2#N/A401OPENC19808OPENS1215-09OPEN
3N39402OPENS46809OPENN3315-10OPEN
4N48404OPENC23810OPENC0816-01OPEN
5S37405OPENS31811OPENN2616-02OPEN
6C34407OPENS33812OPENS1516-03OPEN
7S25408OPENSub815OPENN0616-04OPEN
8SUB410OPENN43816OPENN1816-05OPEN
9SUB411OPENC24817OPENN5216-06OPEN
10SUB412OPENC25819OPENS0616-07OPEN
Busses by Bus #
Cell Formulas
RangeFormula
A2A2=INDEX('Long List'!$A$1:$A$272,MATCH(B2,'Long List'!$B$1:$B$272,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K2:K10Cell Valuecontains "(LOA)"textNO
K2:K10Cell Valuecontains "OPEN"textNO
C2:C10Cell Valuecontains "(LOA)"textNO
C2:C10Cell Valuecontains "OPEN"textNO
G10Cell Valuecontains "(LOA)"textNO
G10Cell Valuecontains "OPEN"textNO
E7Cell Valuecontains "(LOA)"textNO
E7Cell Valuecontains "OPEN"textNO
G7Cell Valuecontains "(LOA)"textNO
G7Cell Valuecontains "OPEN"textNO
E10Cell Valuecontains "(LOA)"textNO
E10Cell Valuecontains "OPEN"textNO
G10Cell Valuecontains "(LOA)"textNO
G10Cell Valuecontains "OPEN"textNO
E9Cell Valuecontains "(LOA)"textNO
E9Cell Valuecontains "OPEN"textNO
G10Cell Valuecontains "(LOA)"textNO
G10Cell Valuecontains "OPEN"textNO
G9Cell Valuecontains "(LOA)"textNO
G9Cell Valuecontains "OPEN"textNO
E8Cell Valuecontains "(LOA)"textNO
E8Cell Valuecontains "OPEN"textNO
G8Cell Valuecontains "(LOA)"textNO
G8Cell Valuecontains "OPEN"textNO
E10Cell Valuecontains "(LOA)"textNO
E10Cell Valuecontains "OPEN"textNO
G10Cell Valuecontains "(LOA)"textNO
G10Cell Valuecontains "OPEN"textNO
I2Cell Valuecontains "(LOA)"textNO
I2Cell Valuecontains "OPEN"textNO
I3Cell Valuecontains "(LOA)"textNO
I3Cell Valuecontains "OPEN"textNO
I2Cell Valuecontains "(LOA)"textNO
I2Cell Valuecontains "OPEN"textNO
I2:I4Cell Valuecontains "(LOA)"textNO
I2:I4Cell Valuecontains "OPEN"textNO
I2Cell Valuecontains "(LOA)"textNO
I2Cell Valuecontains "OPEN"textNO
I2:I4Cell Valuecontains "(LOA)"textNO
I2:I4Cell Valuecontains "OPEN"textNO
I6Cell Valuecontains "(LOA)"textNO
I6Cell Valuecontains "OPEN"textNO
I10Cell Valuecontains "(LOA)"textNO
I10Cell Valuecontains "OPEN"textNO
I9Cell Valuecontains "(LOA)"textNO
I9Cell Valuecontains "OPEN"textNO
I7Cell Valuecontains "(LOA)"textNO
I7Cell Valuecontains "OPEN"textNO
I7:I8Cell Valuecontains "(LOA)"textNO
I7:I8Cell Valuecontains "OPEN"textNO
I6Cell Valuecontains "(LOA)"textNO
I6Cell Valuecontains "OPEN"textNO
I10Cell Valuecontains "(LOA)"textNO
I10Cell Valuecontains "OPEN"textNO
G8Cell Valuecontains "(LOA)"textNO
G8Cell Valuecontains "OPEN"textNO
E5Cell Valuecontains "(LOA)"textNO
E5Cell Valuecontains "OPEN"textNO
G5Cell Valuecontains "(LOA)"textNO
G5Cell Valuecontains "OPEN"textNO
E8Cell Valuecontains "(LOA)"textNO
E8Cell Valuecontains "OPEN"textNO
G8Cell Valuecontains "(LOA)"textNO
G8Cell Valuecontains "OPEN"textNO
E7Cell Valuecontains "(LOA)"textNO
E7Cell Valuecontains "OPEN"textNO
G8Cell Valuecontains "(LOA)"textNO
G8Cell Valuecontains "OPEN"textNO
G7Cell Valuecontains "(LOA)"textNO
G7Cell Valuecontains "OPEN"textNO
G2,E2Cell Valuecontains "(LOA)"textNO
G2,E2Cell Valuecontains "OPEN"textNO
G9Cell Valuecontains "(LOA)"textNO
G9Cell Valuecontains "OPEN"textNO
E6Cell Valuecontains "(LOA)"textNO
E6Cell Valuecontains "OPEN"textNO
G6:G10Cell Valuecontains "(LOA)"textNO
G6:G10Cell Valuecontains "OPEN"textNO
G6Cell Valuecontains "(LOA)"textNO
G6Cell Valuecontains "OPEN"textNO
E10Cell Valuecontains "(LOA)"textNO
E10Cell Valuecontains "OPEN"textNO
G10Cell Valuecontains "(LOA)"textNO
G10Cell Valuecontains "OPEN"textNO
E9Cell Valuecontains "(LOA)"textNO
E9Cell Valuecontains "OPEN"textNO
G9Cell Valuecontains "(LOA)"textNO
G9Cell Valuecontains "OPEN"textNO
E8Cell Valuecontains "(LOA)"textNO
E8Cell Valuecontains "OPEN"textNO
G9:G10Cell Valuecontains "(LOA)"textNO
G9:G10Cell Valuecontains "OPEN"textNO
G8Cell Valuecontains "(LOA)"textNO
G8Cell Valuecontains "OPEN"textNO
I10Cell Valuecontains "(LOA)"textNO
I10Cell Valuecontains "OPEN"textNO
I8Cell Valuecontains "(LOA)"textNO
I8Cell Valuecontains "OPEN"textNO
I8:I9Cell Valuecontains "(LOA)"textNO
I8:I9Cell Valuecontains "OPEN"textNO
I7Cell Valuecontains "(LOA)"textNO
I7Cell Valuecontains "OPEN"textNO
I9Cell Valuecontains "(LOA)"textNO
I9Cell Valuecontains "OPEN"textNO
I6Cell Valuecontains "(LOA)"textNO
I6Cell Valuecontains "OPEN"textNO
I5Cell Valuecontains "(LOA)"textNO
I5Cell Valuecontains "OPEN"textNO
I9Cell Valuecontains "(LOA)"textNO
I9Cell Valuecontains "OPEN"textNO
I9:I10Cell Valuecontains "(LOA)"textNO
I9:I10Cell Valuecontains "OPEN"textNO
I8Cell Valuecontains "(LOA)"textNO
I8Cell Valuecontains "OPEN"textNO
G3,E3Cell Valuecontains "(LOA)"textNO
G3,E3Cell Valuecontains "OPEN"textNO
G2,E2Cell Valuecontains "(LOA)"textNO
G2,E2Cell Valuecontains "OPEN"textNO
I5:I8Cell Valuecontains "(LOA)"textNO
I5:I8Cell Valuecontains "OPEN"textNO
I10Cell Valuecontains "(LOA)"textNO
I10Cell Valuecontains "OPEN"textNO
I8Cell Valuecontains "(LOA)"textNO
I8Cell Valuecontains "OPEN"textNO
I8:I9Cell Valuecontains "(LOA)"textNO
I8:I9Cell Valuecontains "OPEN"textNO
I7Cell Valuecontains "(LOA)"textNO
I7Cell Valuecontains "OPEN"textNO
I5:I30Cell Valuecontains "(LOA)"textNO
I5:I30Cell Valuecontains "OPEN"textNO
K11:K25,I5:I25Cell Valuecontains "(LOA)"textNO
K11:K25,I5:I25Cell Valuecontains "OPEN"textNO
L41:L46,A2:A33,U19:U21,W19:W21,Q5:Q29,S5:S29,E2:E29,G2:G29Cell Valuecontains "(LOA)"textNO
L41:L46,A2:A33,U19:U21,W19:W21,Q5:Q29,S5:S29,E2:E29,G2:G29Cell Valuecontains "OPEN"textNO
 
Upvote 0
Bus Route Listing.xlsx
ABC
1ROUTEBUS #DRIVER
2B0119-72OPEN
3B02912OPEN
4B0317-11OPEN
5B04805OPEN
6B0519-66OPEN
7B0619-74OPEN
8B0717-13 915OPEN
9B0819-63OPEN
10B0919-71OPEN
11B1019-58OPEN
12B1119-75OPEN
13B1219-78OPEN
14B1319-70OPEN
15B1419-64OPEN
16B15611OPEN
17B1619-67OPEN
18B1710-12OPEN
19B1819-77OPEN
20B1916-17OPEN
21B20916OPEN
22B2110-14OPEN
23B22914OPEN
24B23913OPEN
Long List
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11Cell Valuecontains "(LOA)"textNO
A11Cell Valuecontains "OPEN"textNO
A94:A97,C94:C97,A2:A10,G1:G26,E1:E33,I1:I27,K1:K27,L1:L18,N1:N18,T5:T14,V5:V14,P1:P17,R1:R17,C37:C57,A12:A29,C70:C91,A37:A91,A199:A272,C199:C271Cell Valuecontains "(LOA)"textNO
A94:A97,C94:C97,A2:A10,G1:G26,E1:E33,I1:I27,K1:K27,L1:L18,N1:N18,T5:T14,V5:V14,P1:P17,R1:R17,C37:C57,A12:A29,C70:C91,A37:A91,A199:A272,C199:C271Cell Valuecontains "OPEN"textNO
 
Upvote 0
Thanks for that, I suspect that the problem is that you have a mix of numbers & text, try
Excel Formula: 
=XLOOKUP(B2&"",'Long List'!$B$2:$B$300&"",'Long List'!$A$2:$A$300,"n/a",0)
 
Upvote 0
Fluff said:
Thanks for that, I suspect that the problem is that you have a mix of numbers & text, try
Excel Formula: 
=XLOOKUP(B2&"",'Long List'!$B$2:$B$300&"",'Long List'!$A$2:$A$300,"n/a",0)
Click to expand...
I plugged that formula in and I am getting a return of #NAME?
 
Upvote 0
That suggests that either you are not running xl365, or you are missing a number of updates.
How about
Excel Formula: 
=INDEX('Long List'!$A$1:$A$300,MATCH(B2&"",'Long List'!$B$1:$B$300&"",0))
 
Upvote 0
Fluff said:
That suggests that either you are not running xl365, or you are missing a number of updates.
How about
Excel Formula: 
=INDEX('Long List'!$A$1:$A$300,MATCH(B2&"",'Long List'!$B$1:$B$300&"",0))
Click to expand...
No luck on that either. Displays #NA
 
Upvote 0
It works for me
+Fluff 1.xlsm
ABC
1RTEBUS #DRIVER
2B2110-14OPEN
3B02912OPEN
4B0317-11OPEN
5B0519-66OPEN
6B0717-13 915OPEN
7B1019-58OPEN
8B15611OPEN
9B1710-12OPEN
10B1219-78OPEN
Master
Cell Formulas
RangeFormula
A2:A10A2=INDEX('Long List'!$A$1:$A$300,MATCH(B2&"",'Long List'!$B$1:$B$300&"",0))


Would you be willing to share your workbook?
If so upload it to a share site such as OneDrive, GoogleDrive, mark for sharing & then post the link you are given to the thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Index Match after sorting
Replies
1
Views
259
Eric W
Eric W
S
  • Solved
Dynamic Change the Xlookup Return Array
Replies
3
Views
533
Fluff
Fluff
B
  • Question
Update Pivot Tables with Refresh
Replies
1
Views
112
shift-del
shift-del
C
  • Question
Comparing 3 tables to a list of numbers, if match, return 4 columns worth of data on another tab
Replies
4
Views
74
alansidman
A
S
  • Question
Auto pick up next number
Replies
2
Views
91
springran
S

Forum statistics

Threads
1,214,646
Messages
6,120,717
Members
448,985
Latest member
chocbudda

Share this page

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
Back
Top