Sort VLOOKUP data

Question

I have a list of residents that is created with VLOOKUP FORMULAS

101	LALANCETTE	Charlebois	NOELLA	0	845-6056	27-06-1929
102.1	ÉMOND	0	ARTHUR	0	632-2997	16-11-1924
102.2	ÉMOND	Bolduc	JEANNINE	0	632-2997	14-09-1931
103	ARCIERIE	Paulhus	PAULINE	0	845-4748	10-06-1934
104	BEAUVAIS	Létourneau	THÉRÈSE	0	632-2589	29-07-1928
105	HARVEY	0	MARIANNA	0	632-7524	21-07-1917

Column A is ='Residents'!A1

Columns B, C, D, E, F and G are =VLOOKUP(101,'Residents'!A1:Z500,2,FALSE)

What I need is for the information, which comes from the tab entitled "Residents" be sorted alphabetically based on columns B and D on tab "Alphabetical"

Is that possible?

Thanks

Jeannie

Answer 1

The example of a VLOOKUP() formula you provided was,

=VLOOKUP(101,'Residents'!A1:Z500,2,FALSE)

This would be changed to,

=VLOOKUP(A1,'Residents'!A1:Z500,2,FALSE)

=VLOOKUP(A1,'Residents'!A1:Z500,3,FALSE)

=VLOOKUP(A1,'Residents'!A1:Z500,4,FALSE)

Note that the 2, 3, 4, etc will change depending upon what column of information you want returned from the 'Residents'!A1:Z500 data matrix. With these in place, you should be able to select A1, tap Ctrl+A then Data, Sort.

Answer 2

OK, I changed my reference to an absolute reference to read ='Residents'!$A$1, ='Residents'!$A$2, etc.

However, what is the formula that I need to apply to the "Alphabetical" worksheet to have it sort automatically whenever a change is done to the "Resident" worksheet

Thanks for your help

Jeannie

Answer 3

The problem lies in the relative cell reference used in ='Residents'!A1. The row number needs to be locked as absolute like this ='Residents'!A$1 or you need to copy and Paste Special, Value to remove the relative reference. The latter is likely preferred as the former cannot be filled down to cover a lot of values returned to column A. After you get an absolute reference or value in column A the VLOOKUP()'s will sort.

Answer 4

The VLOOKUP is in fact looking as the apartment number which is in the "Residents" tab in Column A as the source.  I do not quite understand what you mean by "reordered" as I want to sort by name on the Alphabetical tab

Could you be more specific

Thanks

Jeannie

Answer 5

That is going to depend upon how your VLOOKUP() works. Looking at your data, it would seem that you are using column A as the source of the lookup_value for VLOOKUP(). These would need to be reordered if you want to reorder the VLOOKUP() returns from other columns.

Of course, you could always use Copy, Paste Special, Values and then sort them manually but you would be losing the dynamic lookup that the VLOOKUP() formulas are providing.