Excel: Vlookup in named range and return a value outside of range

Question

I need to find a way to search for a value in a named range and to return the column next to that range. The reason is simple: I do use list validation with a named range in column A. In this list there are complete name of product types (ex. Relay, Contactor, Enclosure) and I need to return a short description from column B (ex. Rly, Cont, Encl) so that when the user searches the list it's easy to find what he is looking for. I know I could extend my range to $A:$B but if I do then all the values of $B will be included in the list...

This shows a way to figure out what I need: =VLOOKUP(A1;"RANGE+1C",2,0) I tried so many ways with offset and index but couldn't find a way to do so... I even thought it would be possible of doing it with relative reference (ex. =VLOOKUP(A1,Description;1"RC[1]",0) or something like that... Also looked in Google to find some info but it seems to be something very unfamiliar...

I need to do this in a formula and not in VBA.

Here is the link to an example file: http://www.filedropper.com/descriptionbuilder

Any hint?

Thank you!

Answer 1

What you probably want instead of VLOOKUP is the MATCH & INDEX combo.

MATCH is a function which tells you the row number where a duplicate value is found from your selected list - it's like half of VLOOKUP. INDEX is a function which pulls a value out of a list, based on its position # that you give it - it's like the other half of VLOOKUP.

I see your one Range in column A with your ID data is Description - I'll assume that column B has no 'named' range that aligns with yours. I'll assume that column B has other data, and that column A starts somewhere after row one [specifically, I'll assume it starts at row 5]. Together the formula would look like this:

=INDEX(B:B,ROW(A5)-1+MATCH(A1,Description,0))

This says: find the row # within Description that matches the text in A1. Then add the row # of A5 - 1 (this then alignts the top of Description with the top of column B). Then pull that value from column B.

Alternative Method using VLOOKUP and OFFSET

Another method would be to first define how many rows are in Description, and then using that information and the OFFSET function, create a new area which essentially represents Description but going across column A & B instead of just column A. This would look as follows (again, assuming Description starts at A5):

=VLOOKUP(A1,OFFSET(A5,0,0,ROWS(Description),2),2,0)

This says: count how many rows are in Description. Build an array with OFFSET which starts at A5, with as many rows as there are in Description, and 2 columns. (So with 3 items in Description, this would be A5:B7). Then use that new Range in VLOOKUP, and try to find A1 the Description area in column A, and return the result from that row in column B.

Answer 2

You can use Offset to expand a named range by one column and then feed it into Vlookup Something like:

=VLOOKUP(C1,OFFSET(RANGE,0,0,,1+COLUMNS(RANGE)),1+COLUMNS(RANGE))

To test is, I created a named range (called "RANGE") in Column A, I placed values in Column B adjacent to the named range, put a lookup value in C1 and the entered the above formula. It succeeds in retrieving the desired value.

On edit: I think that Grade'Eh'Bacon's answer is excellent and is probably the best approach to this specific problem, but it strikes me as potentially useful to know how to expand the reference of a named range without changing the name itself, so I am keeping this answer.