The problem here is extraction of multiple results from a two column array through a vlookup
The data available is given in columns B and D. The 4 regions along with their major cities are known. The data needs to be arranged as in the table below
Start by sorting on region (column B)
Column A is populated with serial numbers such that each region has a fresh series
Cell A3=1, Cell A4=IF(B4=B3,A3+1,1) – drag till end of column
Column C is concatenation of columns B and A
Cell C3=CONCATENATE(B3,A3) – drag till end of column
This enables us to have a one on one mapping with region and cities
Now, to generate the result table on the right
Cells – G1:J1 is manually populated with numbers 1 through 4
Cell G2=VLOOKUP(CONCATENATE($F2,G$1),$C$3:$D$18,2,FALSE) – drag till end of column and end of row
’tis done!
Alternative solution 1 using offset
To generate the table in F11:G14
Populate F11 to F14 with East, West, North, South
Here again, we would have to sort on region (column B) first
Enter G11 = IF((COLUMN()-COLUMN($G$1))<COUNTIF($B$3:$B$18,$F11),OFFSET($D$3,MATCH($F11,$B$3:$B$18,0)-1+(COLUMN()-COLUMN($G$1)),0,1,1),””)
Here,
column()-column($G$1) generates numbers 0,1 etc
countif – returns number of occurences of east, west, etc
offset – offsets from the reference cell D3, number of rows given by first occurence of east – provided by match function + serial number given by the column function
You could seperate each of the functions to understand how this works. If you need any help in understanding do let me know
Drag to the right and below to populate the entire table
’tis done again
Alternative solution 2 using index
To generate the table in F18:G21
Populate F18 to G21 with East, West, North, South
Enter G18 =IF((COLUMN()-COLUMN($G$1))<COUNTIF($B$3:$B$18,$F11),INDEX($D$3:$D$18,MATCH($F11,$B$3:$B$18,0)+(COLUMN()-COLUMN($G$1))))
Drag to the right and down to populate the entire table.
’tis done once again
[…] populated some cells with a series of numbers (numbers 1-4 were manually populated in the post multiple extraction through vlookup). Here is a way we can do away with this by using functions row() and […]
[…] also is a better way for multiple extraction because sorting of original table is not […]