Perform different VLOOKUPs based on value of =RIGHT value

Question

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.

Answer 1

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.