Sort issue - multiple tables using vlookup or index/match

lolbint

New Member
Joined
Jan 31, 2019
Messages
3
hi

I'm pretty new to both tables, VLOOKUP and INDEX/MATCH

I have four tables in separate workbooks, I have made them tables as my brother has been helping me to add pivot tables to analyse the data quickly when I come to end of season reports

I have now played around with adding VLOOKUP or INDEX/MATCH to pull repeated data across to each table rather than me manually inputting it each time, which is how I have done it in the past

I have given each record a unique ID but the problem I am getting is the sort order of the first table is defining the sort order for all the other tables, when I try and sort any column on the other tables it just doesn't work, it won;t work for me if I always have to change the source table order as sometimes I want to sort by columns

I am also having an issue on second table where I have filled dates into cells in a column that is unique to that table but when I sort the source table in a new way say by vehicle reg rather than unique ID those dates stay in the same place and don;t move with the ID they should stay with. It also seems to create multiple entries of same ID in second table when I sort source table in a different way

I ahve tried both VLOOKUP and INDEX/MATCH formulas and I get same problems with both, I have made sure my VLOOKUP has fourth argument of FALSE so its doing exact matchs. I have just tried removing blank rows from my table, now have separate issue that when I add a row to source table it doesn't add that row to second table!

Sorry my knowledge is basic, I may not be trying to do stuff correctly any hints/tips/pointers gratefully received

Table 1 Example of source table
ID
Registration
Driver Name
Hirer
Loler
Defect Report
1
VN04 VCA
Lauren
DVR
1/3/19
2
W644 RHY
Dave
Priority
3
EA59 KSO
Tim
DVR
4
OU51 FGH
Kate
SHB

<tbody>
</tbody>

Table 2 example

ID
Registration
Driver
Hirer
Data
1
VN04 VCA
Lauren
DVR
y
2
W644 RHY
Dave
Priority
n
3
EA59 KSO
Tim
DVR
y
4
OU51 FGH
Kate
SHB
n

<tbody>
</tbody>

So table 2 I want to use VLOOKUP or INDEX/MATCH I dont really mind which to pull over ID, Reg, Driver and Hirer but then be able to sort table 2 by Data or sort source table by hirer

these tables will be huge not simple like the examples so even though I can use filters in table 2, 3, 4 to just see a particular criteria of a column I would then need to sort by another criteria, each table will have extra rows in that aren't being pulled over by formulas also so that further complicates matters....

Here is an example of one of VLOOKUP formulas, I have tried adding $ that also doesn;t seem to help

=VLOOKUP(Licences!A:A,Table1[#All],1,FALSE )
=VLOOKUP(Licences!A:A,Table1[#All],1,FALSE )

<colgroup><col width="322"></colgroup><tbody>
</tbody>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

BlueHornet

Active Member
Joined
Apr 13, 2012
Messages
340
Welcome to the board,

I'm not sure that I fully understand your description of the issues to have a complete response at this time, but your sample formulas at the bottom of the message display two crucial errors. That is, the VLOOKUP function (and many others) only operate on single-cell values - you look up ONE value per formula - AND you have to VLOOKUP into the total range of values that you may want returned. That is, you'll want to do something such as
Code:
=VLOOKUP( $B2, 'Licences'!$A$2:$D$500, 2, FALSE)

That example supposes that the key value that will be common to both tables is B2. (Alternatively, you could look up on the ID values in $A2 and below, if those ID numbers correspond throughout, as in the sample.) You want to look up that value in the Licenses worksheet (and specify the full range; I just guessed with my formula), then return, cell-by-cell, the values that you want. If you're looking up B2 in a table that extends from columns A:D, then you can return ONLY values from column A, B, C or D. If you want to get a value from column E, then that has to be included in the VLOOKUP formula.

That function will work in ONE cell. Once you've got that working, then you should be able to copy the entire formula as-is to all of the other cells in the table for which you want values.

See where that gets you, and come back with additional questions that you'll likely have as you get into this.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,579
Members
430,557
Latest member
MK15

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