Way to reference a modified range with single cell??

[markgoolsbay]

Hello,

I want to apply a formula that searches a range modified by another formula. For instance, if you had a column with some unimportant two-character prefix and you wanted to perform a VLOOKUP on the 5 trailing characters, is there a way to do so without first creating a separate column using the formula, =MID(text, 3, 5) ?

It would look something like this... =VLOOKUP( D1, MID(A:A,3,5) , 2 , FALSE)

A:A being the cells with the extra 2 character prefix, MID(A:A,3,5)being your modified range, and D1 being the matching cell after all cells in A:A range are trimmed.Thanks!


Mark

 

[Max1616]

I would use an array formula for this:

{=INDEX(B:B,MAX(IF(MID(A:A,3,5)=TEXT(D1,"#"),ROW(A:A),0)))}
where B:B is the column you would like to return (in your formula it's represented by the 2 before the False)


To create an array formula, put this formula into a cell: =INDEX(B:B,MAX(IF(MID(A:A,3,5)=TEXT(D1,"#"),ROW(A:A),0))) Then double click in cell and press Cntrl+Shift+Enter. Brackets will appear around the formula to indicate this was done correctly.

Let me know if you have any questions!

-Max

 

[Tetra201]

Maybe

=VLOOKUP("??"&D1,A:A,1,FALSE)

 

[Max1616]

Haha idk why I didn't think of that! Tetra has a much more efficient solution than mine!