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>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

BlueHornet

Active Member
Joined
Apr 13, 2012
Messages
338
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,435
Messages
5,528,746
Members
409,833
Latest member
tdnhan

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top