SOLVED

Vlookup nesting

New Contributor

Hi,

 

I have a list of postcodes form participants that I need to assign a "remoteness" area to.  They include Major cities of Australia, Inner regional, Outer regional, remote and

Very remote.

These remoteness areas are listed by the Australian Bureau of statistics and they are classified by postcode, but sometimes the post code spans more than one remoteness area. In this case I would like to use the remoteness area to which the largest percentage of the post code is assigned.

 

 

So for example participant 168 had a postcode of 2158 and the VLOOKUP function returned a Inner regional Australia and it should have been major cities because it has the larger %

kmat3540_2-1626591638865.png

it should have been major cities

kmat3540_3-1626591854386.png

Some postcodes have more than two postcodes. I essentially want it to pick the one with the largest %.

 

The code I used was =VLOOKUP(G8,$A$8:$B$3188,2,TRUE)

kmat3540_5-1626592293876.png

Any help would be appreciated I have been at this for hours

The file is attached

Kind Regards,

 

Karen

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@kmat3540 Sort your data by percentage and use "FALSE" as the last argument in the VLOOKUP function. Then it will work.

File attached.

Thanks so much @Riny_van_Eekelen .

 

It worked. The solution was simpler that I thought. 

 

Cheers

 

Karen