This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(217.60000000000002, 59%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETC%3C/text%3E%3C/svg%3E)
dear support,
i have a data set in excel as a list of events. for 1 specific day there could be many events for the day. i am trying to use excel 365 with vlookup to interogate a number and extract the value of left position 1, 2 and right , 1 i have managed to figure out the value to maintain the number which is hte value in the special events id field. i have mastered 2 vlook ups in hte same formulae concat usin &char(1)& and formatin the output sell to wrap text. my aim is to able to configure the number to look up as a dynamic feature thus allowing each day to have a defferant set of special events. i am doing this for a user say my grandmother so i dont want to have to reconfigure hte formulae every time thre is a change.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
It would help us provide a meaningful response if you showed us some sample data and the desired result. Also show us the formulas you are currently using.
speacial event id Event description
1.00 bingo
2.00 quiz
3.00 sensory dog
4.00 neurophyscologist
5.00 halloween party
6.00 xmas party
7.00 coffee morning
8.00 annual general meeting
cell g28 728 formulae =VLOOKUP(VALUE(LEFT(G28,1)),sevents,2,TRUE)&CHAR(10)&VLOOKUP(VALUE(LEFT(G28,2)),sevents,2,TRUE)&CHAR(10)
i tried to add an exel file it wouldnt let me so i have just added the textthe special event is on a separate worksheet and i named hte range to make hte vlookup easier.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 3%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EI%3C/text%3E%3C/svg%3E)
Hi @Tim Cox,
If you are running Excel 365, then try the formula below. I have applied it to cell E2.
=BYROW(D2:D9, LAMBDA(a, TEXTJOIN(CHAR(10),, IFNA(XLOOKUP(--REGEXEXTRACT(a, "\d", 1), A2:A11, IF(B2:B11 = "", "", B2:B11)), ""))))
Hope this helps.
IlirU
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 87%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJ%3C/text%3E%3C/svg%3E)
Hi Tim
The following provides a solution based on the data you provided.
For illustration purposes, in the picture below
=TEXTJOIN(" - ",TRUE,IFERROR(VLOOKUP(VALUE(MID(G2,1,1)),sevents,2,FALSE),""),IFERROR(VLOOKUP(VALUE(MID(G2,2,1)),sevents,2,FALSE),""),IFERROR(VLOOKUP(VALUE(MID(G2,3,1)),sevents,2,FALSE),""))
** I used " - " as the event delimiter in the formula.
** Adapt the formula according to your scenario
IMPORTANT NOTE:
My concern with this table setup is that you are limited to at most 10 events in the table, using digits 0 to 9.
If you enter more than 10, then you need to consider another way to enter the set of event IDs
I hope this helps you and gives a solution to your problem.
Do let me know if you need further help.
Kind regards
Jeovany CV
While left and right will allow you to extract the 1st and 3rd digits, you need to use mid(x,2,1) to extract the middle digit. From an ease of program maintenance point of view, it also makes sense to use mid for all 3 digits.
If your data is in A1:B9 and your dynamic criteria is in F1, then this formula will do what you describe:
Visual Basic for Applications
=LET(data,A1:B9,look,LAMBDA(digit,VLOOKUP(VALUE(MID(F1,digit,1)),data,2)),look(1)&CHAR(10)&look(2)&CHAR(10)&look(3))
The cell containing the formula needs to be wide enough to hold the longest string in column B, tall enough for three lines, and have the Wrap text format checked
barry, sorry but im not creating any vb function here, im just wanting a user to ut in a number eg 278 and it pull out 3 options, then by using the format cell option wrap the text based on char (10 between each vlookup that is concatenated to gether. with the minimum of eas for the user ie they no th events ids and can manipulate the output by just changing the lookup number, it may not be possible, i have managed to get and out come by using the exact values individually in each vlookup that i join ogethe with &char(10)& but when i use the value(left(cellref,1Or2 or 3) i get a error i think im trying to do something that int possible
My solution did not create a VB function. It used a regular Excel function to compute a value based on the data you provided and stores the result of that computation in a single cell. It is no different than the formula you provided in your original question.