=VLOOKUP(E4,Serial_Prefix,2) Return value in next row.

P

PCLIVE

=VLOOKUP(E4,Serial_Prefix,2)

How can I write this formula so that it returns the value in the very next
row of what it finds? For example, this formula returns the value in L4.
How can I alter the formula so that it adds one row to the result, therefore
returning the value in M4?

Thanks,
Paul
 
N

Niek Otten

Hi Paul,

=INDEX(Serial_Prefix,MATCH(E4,G1:G12)+1,2)

Where G1:G12 is the first column of Serial_Prefix

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
B

Barb R.

Can you describe what is in "Serial_Prefix". I assume it's an array of data
defined by a NAME. What type of data is in each column of data and what are
the row/column locations for Serial_Prefix.
 
P

PCLIVE

This didn't quite work. Let me see if I can explain this differently.

I've got a list of Prefixe values in column A. In column B I have
corresponding values to those prefixes. All of the prefixes are 3 letters.
I've defined columns A:B as 'Serial_Prefix'.

I previously applied a VLOOKUP formula that would use cell E4 for search
criteria and then return the corresponding value from the table array or
defined name area. I've got the defined name area sorted alphabetically by
column A.
As I start to type in E4, when I have two letters of the three I want to
enter, the formula result seems to return the value in the row just before
the cell that contains the two letters I had typed. Based on the data and
formula below, if I type three letters "ACD", the formula result is '5'. If
I only type two letters "AC", the formula returns the value that corresponds
to "ABD". Is there a way to force it to the next row?

=VLOOKUP(E4,Serial_Prefix,2)

AAA 1
ABB 2
ABC 3
ABD 4
ACD 5
ACE 6
ACR 7

Thanks,
Paul
 
N

Niek Otten

Hi Paul,

Really,

=INDEX(Serial_Prefix,MATCH(E4,A1:A7)+1,2)

Should work

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
P

PCLIVE

Thanks Niek.

You were right. It did work. I made a mistake when I applied it the first
time. I had adjusted your suggested formula and instead of changing column
G to column A, I changed it to column B.
When I applied the appropriate column, everything works great!

Thanks again and sorry for my confusion.

Paul
 
N

Niek Otten

Thanks for the feedback, Paul
Glad it works

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
P

Paul Hightower

Try this...
Redo your array like this
A B C
Code Row Value
AAA 1 13
ABB 2 11
ABC 3 9
ABD 4 7
ACD 5 5
ACE 6 3

Then =VLOOKUP(VLOOKUP(F5,Serial_Prefix,2)+1,B2:C8,2)
The interior lookup finds the row number where the code is. That is used as
the lookup key for the exterior lookup PLUS 1 to find the value of the next
row. This will work for any code to value relationship. Error checking is
needed though as row 7 in this example doesn't exist. You will have to
decide what to do when "ACE" is entered as a code and what value you want to
return to your function.
Paul
ACR 7 1

Then
 
Top