Sorting issues with tables using VLOOK-UP or INDEX-MATCH function

[FraukeJarre]

Dear MrExcel Team,
I created the following table in the same worksheet as the original table (with the source data):
[TABLE="width: 568"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Country[/TD]
[TD]Event 1[/TD]
[TD]Formulatext of Event1[/TD]
[/TR]
[TR]
[TD]LaCan[/TD]
[TD]COLOMBIA[/TD]
[TD]3[/TD]
[TD]=VLOOKUP(A6,A6:B26,2,0)[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]ESTONIA[/TD]
[TD]20[/TD]
[TD]=VLOOKUP(A7,A7:B27,2,0)[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]GERMANY[/TD]
[TD]149[/TD]
[TD]=VLOOKUP(A8,A8:B28,2,0)[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]HUNGARY[/TD]
[TD]113[/TD]
[TD]=VLOOKUP(A9,A9:B29,2,0)[/TD]
[/TR]
[TR]
[TD]AMEA[/TD]
[TD]INDIA[/TD]
[TD]61[/TD]
[TD]=VLOOKUP(A10,A10:B30,2,0)[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]


I then tried to sort the table by "region" rather then by "country" - but the formulas are not sorted and the numbers indicated for the "Event 1" are no longer correct...

[TABLE="width: 568"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Country[/TD]
[TD]Event 1[/TD]
[TD]Formulatext of Event1[/TD]
[/TR]
[TR]
[TD]AMEA[/TD]
[TD]INDIA[/TD]
[TD]3[/TD]
[TD]=VLOOKUP(A6,A6:B26,2,0)[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]ESTONIA[/TD]
[TD]20[/TD]
[TD]=VLOOKUP(A7,A7:B27,2,0)[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]GERMANY[/TD]
[TD]149[/TD]
[TD]=VLOOKUP(A8,A8:B28,2,0)[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]HUNGARY[/TD]
[TD]113[/TD]
[TD]=VLOOKUP(A9,A9:B29,2,0)[/TD]
[/TR]
[TR]
[TD]LaCan[/TD]
[TD]COLOMBIA[/TD]
[TD]61[/TD]
[TD]=VLOOKUP(A10,A10:B30,2,0)[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

I then tried the same with the INDEX-MATCH function "=INDEX(B6:B26,MATCH(A6,A6:A26,0))" as I thought it would be a VLOOKUP issue. But again when sorting the table - the formula is not sorted...
Do you know what I can do to get also the formulas sorted?

Thanks so much in advance for your help!

 

[Coding4Fun]

Maybe?

=VLOOKUP(A6,$A$6:$B$26,2,0)

 

[Drrellik]

remember Vlookup needs the table to be 'A-Z' and based on your Table / formula you are bordering on a circular reference why are you looking for the Value in A6 in table A6:B26 you are not really doing anything.

As for sorting set your table up first A-Z for the reference column, then set your formula and table range after that.

HTH

 

[Aladin Akyurek]

@ FraukeJarre

You should have just one formula under Event 1.

What is the range in which your original table is located?

 

[FraukeJarre]

Thanks for all your replies - I fixed the range as indicated by Coding4Fun - and realized that I can sort only once the sheet name is added to the fomula =VLOOKUP(Sheet1!A6,$A$6:$B$26,2,0). So including the sheet name to the formula the sorting is then working!

 

[Drrellik]

[TABLE="class: cms_table, width: 568"]
<tbody>[TR]
[TD]Region
[/TD]
[TD]Country
[/TD]
[TD]Event 1
[/TD]
[TD]Formulatext of Event1
[/TD]
[/TR]
[TR]
[TD]AMEA
[/TD]
[TD]INDIA
[/TD]
[TD]3
[/TD]
[TD]=VLOOKUP(A6,A6:B26,2,0)
[/TD]
[/TR]
[TR]
[TD]EU
[/TD]
[TD]ESTONIA
[/TD]
[TD]20
[/TD]
[TD]=VLOOKUP(A7,A7:B27,2,0)
[/TD]
[/TR]
[TR]
[TD]EU
[/TD]
[TD]GERMANY
[/TD]
[TD]149
[/TD]
[TD]=VLOOKUP(A8,A8:B28,2,0)
[/TD]
[/TR]
[TR]
[TD]EU
[/TD]
[TD]HUNGARY
[/TD]
[TD]113
[/TD]
[TD]=VLOOKUP(A9,A9:B29,2,0)
[/TD]
[/TR]
[TR]
[TD]LaCan
[/TD]
[TD]COLOMBIA
[/TD]
[TD]61
[/TD]
[TD]=VLOOKUP(A10,A10:B30,2,0)
[/TD]
[/TR]
</tbody>[/TABLE]


I am glad you got it working, but I guess I am lost now, because the formula you have to me looks for the value of A10, in table A10:B30, and returns column 2, to me you could just use =B10 because you are asking it to find a value from inside the same table....

again I am glad it is working for you.

~DR