Summary:
I am needing to =VLOOKUP different column numbers based on the outcome of a =RIGHT statement. My current formula performs this as expected, but isn't easily scalable should I need to add more potential outcomes of the =RIGHT statement. Is there any way to scale this effectively?
Use Case:
I have a formula that inserts either "Use 1", "Use 2", "Use 3", "Use 4" or "Unknown", into Column A. Column B then need to perform a =VLOOKUP, which will vary depending on what the last character is in column A and return a different column number. For example, if cell A1 contains the text "Use 1", I will need to return the 2nd column in the =VLOOKUP. If cell A2 contains "Use 4", I will need to return the 8th column in the =VLOOKUP (the column number is non-linear).
Current Code:
=IFS(RIGHT(A2,1)="1",VLOOKUP(B2,'Table2'!A:I,2,FALSE),RIGHT(A2,1)="2",VLOOKUP(B2,'Table2'!A:I,4,FALSE),RIGHT(A2,1)="3",VLOOKUP(B2,'Table2'!A:I,5,FALSE),RIGHT(A2,1)="4",VLOOKUP(B2,'Table2'!A:I,8,FALSE),RIGHT(A2,1)="N","NotFound")
Example Table using above code:
- Code Table
| Check | ThingToFind | Code Output |
|---|---|---|
| Use 1 | FOO | ABC |
| Use 3 | BAR | 012 |
| Use 4 | BAZ | IN |
| Unknown |
- Table2
| ThingToFind | Data1 | Data2 | Data3 | Data4 | Data5 | Data6 | Data7 | Data8 |
|---|---|---|---|---|---|---|---|---|
| FOO | ABC | DEF | GHI | JKL | MNO | PQR | STU | VWX |
| BAR | 123 | 456 | 789 | 012 | 345 | 678 | 901 | 234 |
| BAZ | SOME | THING | TO | LOOK | UP | GOES | IN | HERE |
Conclusion:
The above example works fine for the previous use case, but I am trying to make this scalable without needing to edit the formula every time the Check column may get new additions (such as "Use 5" or "Use 12". Is there any way of doing this practically? My thought was to use a separate table to =VLOOKUP the column number (so I would only need to edit this table and not the formulas each time), but it appears I am unable to use a different =VLOOKUP for the COL_INDEX_NUM portion of the initial =VLOOKUP. Any help with this would be appreciated. I am using MSO 365.
CodePudding user response：
Your options seem to be completely arbitrairy, and since the options under 'use' seem lineair to those, I'd suggest to use CHOOSE():
Formula in C2:
=IFERROR(VLOOKUP(B2,E$2:M$4,CHOOSE(--MID(A2,5,99),2,4,5,8),0),"")
Dragged down...
The logic here is:
--MID(A2,5,99)- Used to grab the number from the A-column. These seem to be lineair (or will result in an error if 'Unknown').- The result of the above is input for the 1st parameter of
CHOOSE(), and since they are lineair we can then write each value in the 2nd up to 5th parameter. Scale this if needed. - The
VLOOKUP()works as expected and should now return the columns as per outcome above. IFERROR()is there to catch any error given by 'Unknown'.
Since BYROW() appears to be in production, it won't harm to add the alternative to create a single formula for a spilling array. This would mean you only ever need to adjust a single formula:
Formula in C2:
=BYROW(A2:B5,LAMBDA(X,IFERROR(VLOOKUP(INDEX(X,2),E$2:M$4,CHOOSE(--MID(INDEX(X,1),5,99),2,4,5,8),0),"")))
Btw, kudos for well written questions!
CodePudding user response：
Kind of dirty but works perfect:
Aboe your Table2, add a transposed row with those Use 1, Use 2 ...Use N in the position where you want the value to be extracted. Then combine VLOOKUP with MATCH.
My formula in column D is:
=VLOOKUP(B3;$A$14:$I$16;MATCH(A3;$A$12:$I$12;0);FALSE)
You cam trap this into an IF.ERROR or IF for the Unknown option.
The advantage of this approach is that you can add/switch more Use Neasily just changing the target ranges.