I am trying to figure out how to use the Lookup formulas in Excel to get what I need. I have a 2x100 table of values. The left column, "A", has a series of unique numbers. The right column. "B" has several repeated values, as many as 10 of each value, so not unique. EX: Source Table A B111 Squeeze112 SacBnt113 Sneaky114 PulBak115 Bnt&Rn121 Slap122 FakTak123 Take124 Steal125 Ht&Rn131 Delay132 Cancel133 Repeat134 Squeeze135 SacBnt141 Sneaky142 PulBak143 Bnt&Rn144 Slap145 FakTak151 Take152 Steal153 Ht&Rn154 Delay155 Cancel211 Repeat212 Squeeze213 SacBnt214 Sneaky215 PulBak221 Bnt&Rn222 Slap223 FakTak224 Take225 Steal231 Ht&Rn232 Delay233 Cancel234 Repeat235 Squeeze241 SacBnt242 Sneaky243 PulBak244 Bnt&Rn245 Slap251 FakTak252 Take253 Steal254 Ht&Rn255 Delay311 DrgBnt312 Repeat313 Squeeze314 SacBnt315 Sneaky321 PulBak322 Bnt&Rn323 Slap324 FakTak325 Take331 Steal332 Ht&Rn333 Delay334 DrgBnt335 Repeat341 Squeeze342 SacBnt343 Sneaky344 PulBak345 Bnt&Rn351 Slap352 FakTak353 Take354 Steal355 Ht&Rn411 Delay412 DrgBnt413 Repeat414 Squeeze415 SacBnt421 Sneaky422 PulBak423 Bnt&Rn424 Slap425 FakTak431 Take432 Steal433 Ht&Rn434 Delay435 DrgBnt441 Repeat442 Squeeze443 SacBnt444 Sneaky445 PulBak451 Bnt&Rn452 Slap453 FakTak454 Take455 Steal In a separate table I need to look for the lowest "A" value of each "B" value, then the next lowest, etc... So in the top row of the following table, I need the lowest "A" value of "Take" from the first table, which is 123, then the next lowest is 151, then 224, etc... EX: I need to end up with this table after referencing the source table. Take 123 151 224 252 325 353 431 454FakTak 122 145 223 251 324 352 425 453Bnt&Rn 115 143 221 244 322 345 423 451DrgBnt 311 334 412 435 SacBnt 112 135 213 241 314 342 415 443Sneaky 113 141 214 242 315 343 421 444Squeeze 111 134 212 235 313 341 414 442PulBak 114 142 215 243 321 344 422 445Slap 121 144 222 245 323 351 424 452Ht&Rn 125 153 231 254 332 355 433Steal 124 152 225 253 331 354 432 455Delay 131 154 232 255 333 411 434Cancel 132 155 233 Repeat 133 211 234 312 335 413 441 I've attached a file with the source and output tables.

DonMirabella L1=UNIQUE(Source!B1:B100) M1=TRANSPOSE(SORT(FILTER(Source!$A$1:$A$100,(Source!$B$1:$B$100=L1))))

DonMirabella You can use the following 2 formulas:to create the unique list from B:=UNIQUE(B:B)Then to create the list you want:=INDEX(TRANSPOSE(FILTER(A:B,B:B=E1)),1)where the original list is in columns A and B and the new lists are in E and F. You then copy that formula down

mtarler We don't have Excel 365 so no Unique function available.

DonMirabella If you are on Excel 2019 you can use TEXTJOIN in a three step solution as shown in the attached workbook. First create a pivot table, then TEXTJOIN, and then text-to-columns. Not very elegant, but fairly easy and workable if this is something you do once or only every now and then. Otherwise, use PowerQuery. Query the table in the two columns, add an index column, pivot the index with column A as the value (without aggregation). Then merge all the columns except the first one, using a space as delimiter. Trim the lot and split the column by space delimiter to get the table you desire. Load back to Excel.

DonMirabella As variant you may create PivotTable on the range with adding data to data model and create the measure to add the numbers as Numbes:=CONCATENATEX(Range,Range[Number],", ") Result is

Forum Discussion

DonMirabella

Copper Contributor

Oct 16, 2020

Reference Question

I am trying to figure out how to use the Lookup formulas in Excel to get what I need.

I have a 2x100 table of values. The left column, "A", has a series of unique numbers. The right column. "B" has several repeated values, as many as 10 of each value, so not unique.

EX: Source Table

A B

111 Squeeze
112 SacBnt

113 Sneaky

114 PulBak

115 Bnt&Rn

121 Slap

122 FakTak

123 Take

124 Steal

125 Ht&Rn

131 Delay

132 Cancel

133 Repeat

134 Squeeze

135 SacBnt

141 Sneaky

142 PulBak

143 Bnt&Rn

144 Slap

145 FakTak

151 Take

152 Steal

153 Ht&Rn

154 Delay

155 Cancel

211 Repeat

212 Squeeze

213 SacBnt

214 Sneaky

215 PulBak

221 Bnt&Rn

222 Slap

223 FakTak

224 Take

225 Steal

231 Ht&Rn

232 Delay

233 Cancel

234 Repeat

235 Squeeze

241 SacBnt

242 Sneaky

243 PulBak

244 Bnt&Rn

245 Slap

251 FakTak

252 Take

253 Steal

254 Ht&Rn

255 Delay

311 DrgBnt

312 Repeat

313 Squeeze

314 SacBnt

315 Sneaky

321 PulBak

322 Bnt&Rn

323 Slap

324 FakTak

325 Take

331 Steal

332 Ht&Rn

333 Delay

334 DrgBnt

335 Repeat

341 Squeeze

342 SacBnt

343 Sneaky

344 PulBak

345 Bnt&Rn

351 Slap

352 FakTak

353 Take

354 Steal

355 Ht&Rn

411 Delay

412 DrgBnt

413 Repeat

414 Squeeze

415 SacBnt

421 Sneaky

422 PulBak

423 Bnt&Rn

424 Slap

425 FakTak

431 Take

432 Steal

433 Ht&Rn

434 Delay

435 DrgBnt

441 Repeat

442 Squeeze

443 SacBnt

444 Sneaky

445 PulBak

451 Bnt&Rn

452 Slap

453 FakTak

454 Take

455 Steal

In a separate table I need to look for the lowest "A" value of each "B" value, then the next lowest, etc... So in the top row of the following table, I need the lowest "A" value of "Take" from the first table, which is 123, then the next lowest is 151, then 224, etc...

EX: I need to end up with this table after referencing the source table.

Take 123 151 224 252 325 353 431 454
FakTak   122 145 223 251 324 352 425 453
Bnt&Rn 115 143 221 244 322 345 423 451
DrgBnt   311 334 412 435
SacBnt   112 135 213 241 314 342 415 443
Sneaky   113 141 214 242 315 343 421 444
Squeeze 111 134 212 235 313 341 414 442
PulBak   114 142 215 243 321 344 422 445
Slap        121 144 222 245 323 351 424 452
Ht&Rn   125 153 231 254 332 355 433
Steal       124 152 225 253 331 354 432 455
Delay     131 154 232 255 333 411 434
Cancel    132 155 233
Repeat    133 211 234 312 335 413 441

I've attached a file with the source and output tables.

Sample Table.xlsx12 KB

Formulas and Functions

16 Replies

SergeiBaklan
Diamond Contributor
Oct 16, 2020
DonMirabella

As variant you may create PivotTable on the range with adding data to data model and create the measure to add the numbers as

Numbes:=CONCATENATEX(Range,Range[Number],", ")

Result is

Sample Table.xlsx103 KB
- DonMirabella
  Copper Contributor
  Oct 16, 2020
  SergeiBaklan Thank you Sergei, this looks like the way to go for me. However, when I try to setup my own pivot table in my spreadsheet, the "Add this data to the Data Model" check box is greyed out.
  
  I don't see a reason why nor do I see how to change that.
  
  Don
  - SergeiBaklan
    Diamond Contributor
    Oct 16, 2020
    DonMirabella
    
    Don, not sure what it could be with data model. Try formula solution, perhaps it'll be easier and not necessary to refresh data even if PivotTable works.
Riny_van_Eekelen
Platinum Contributor
Oct 16, 2020
DonMirabella If you are on Excel 2019 you can use TEXTJOIN in a three step solution as shown in the attached workbook. First create a pivot table, then TEXTJOIN, and then text-to-columns. Not very elegant, but fairly easy and workable if this is something you do once or only every now and then.

Otherwise, use PowerQuery.
Query the table in the two columns, add an index column, pivot the index with column A as the value (without aggregation). Then merge all the columns except the first one, using a space as delimiter. Trim the lot and split the column by space delimiter to get the table you desire. Load back to Excel.
donmaribella.xlsx21 KB
mtarler
Silver Contributor
Oct 16, 2020
DonMirabella You can use the following 2 formulas:
to create the unique list from B:
=UNIQUE(B:B)
Then to create the list you want:
=INDEX(TRANSPOSE(FILTER(A:B,B:B=E1)),1)
where the original list is in columns A and B and the new lists are in E and F. You then copy that formula down
- DonMirabella
  Copper Contributor
  Oct 16, 2020
  mtarler We don't have Excel 365 so no Unique function available.
  - SergeiBaklan
    Diamond Contributor
    Oct 16, 2020
    DonMirabella
    
    One more variant
    
    In G4
    
    =IFERROR( INDEX($B$2:$B$101, AGGREGATE(15,6,1/(COUNTIF($G$3:G3,$B$2:$B$101)=0)*(ROW($B$2:$B$101)-ROW($B$1)), 1) ),"")
    
    and drag it down till empty cell appears.
    
    In H4
    
    =TEXTJOIN(", ",1,IF($B$2:$B$101=$G4,$A$2:$A$101,""))
    
    and also drag down
    Sample Table.xlsx106 KB

Detlef_Lewin

Silver Contributor

Oct 16, 2020

DonMirabella

=UNIQUE(Source!B1:B100)

=TRANSPOSE(SORT(FILTER(Source!$A$1:$A$100,(Source!$B$1:$B$100=L1))))