Sort issue - multiple tables using vlookup or index/match

[lolbint]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Registration
[/TD]
[TD]Driver Name
[/TD]
[TD]Hirer
[/TD]
[TD]Loler
[/TD]
[TD]Defect Report
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]VN04 VCA
[/TD]
[TD]Lauren
[/TD]
[TD]DVR
[/TD]
[TD]1/3/19
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]W644 RHY
[/TD]
[TD]Dave
[/TD]
[TD]Priority
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]EA59 KSO
[/TD]
[TD]Tim
[/TD]
[TD]DVR
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]OU51 FGH
[/TD]
[TD]Kate
[/TD]
[TD]SHB
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2 example

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Registration
[/TD]
[TD]Driver
[/TD]
[TD]Hirer
[/TD]
[TD]Data
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]VN04 VCA
[/TD]
[TD]Lauren
[/TD]
[TD]DVR
[/TD]
[TD]y
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]W644 RHY
[/TD]
[TD]Dave
[/TD]
[TD]Priority
[/TD]
[TD]n
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]EA59 KSO
[/TD]
[TD]Tim
[/TD]
[TD]DVR
[/TD]
[TD]y
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]OU51 FGH
[/TD]
[TD]Kate
[/TD]
[TD]SHB
[/TD]
[TD]n
[/TD]
[/TR]
</tbody>[/TABLE]

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 )

<colgroup><col width="322"></colgroup><tbody>
[TD="class: xl70, width: 322"]=VLOOKUP(Licences!A:A,Table1[#All],1,FALSE )[/TD]

</tbody>

 

[BlueHornet]

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

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