Perform different VLOOKUPs based on value of =RIGHT value

user_17901329 0
Viewed 58

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.

vlookup formula if-statement excel-formula excel
2 Answers

Your options seem to be completely arbitrairy, and since the options under 'use' seem lineair to those, I'd suggest to use CHOOSE():

enter image description here

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:

enter image description here

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!

user_97581940

Kind of dirty but works perfect:

enter image description here

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.

user_91998280
Related