MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vlookup


Posted by Shirley on August 13, 2001 8:36 PM

How do I tell vlookup to go over 5 columns then down one row? or is this a match()index() situation? If so how do I write the formula? I would appreciate any information you can give me.


Posted by Ian on August 13, 2001 10:26 PM

Re:

=vlookup(a1,a2:m10,5,0)+1

just add +1 st the end to go down a row
5 is in thhe column part

ian

Posted by Aladin Akyurek on August 14, 2001 2:26 AM

Shirley,

Care to provide 5 rows of your lookup table (including column headings)?

Aladin

Posted by Mark W. on August 14, 2001 6:45 AM

Re:

+1 will increment the value returned by VLOOKUP --
not "go down a row".

Posted by Ian on August 14, 2001 3:17 PM

Very true Mark...but!

That's true

the table a knocked up was givening me bad info
but I see you didn't provide an answer? just watching are we :)

i'll try again

Try

=INDEX(A1:M10,MATCH(P15,A1:A10,0)+1,5)

where P15 is what your looking for in the first column

Ian

P.S. Sorry I misslead you

Posted by Mark W. on August 14, 2001 3:29 PM

Just waiting...

...for a response to Aladin's request for specifics...

26861.html

Don't want to jump out of the starting block... : )

Posted by Ian on August 14, 2001 4:04 PM

Fair Enough.....

Thought I'd pre-empt the attack
I'll get shot but, hey you never know it might be the one, and I'll go down in a blaze of glory (yeh right :) )

Ian ...for a response to Aladin's request for specifics... 26861.html Don't want to jump out of the starting block... : ) : That's true : the table a knocked up was givening me bad info

Posted by Aladin Akyurek on August 14, 2001 4:09 PM

Ian & Mark...

I hope Shirley doesn't mind this one.

She sent me specs off-line.

The data look like what follows:

{"Project #","Group #",0,"Category","Amount";"4345-AE","001",0,"Labor:",57;0,0,0,"Expense:",0;0,0," Totals:",0,57;"4444-XX","001",0,"Labor:",645.73;0,0,0,"Expense:",114.5;0,0," Totals:",0,760.23;"4111-AA","001",0,"Labor:",1776.22;0,0,0,"Expense:",0;0,0," Totals:",0,1776.22;"4001-BB",0,0,"Labor:",2478.95;0,0,0,"Expense:",114.5;0,0,"Totals:",0,2593.45}

I gave her a formula a bit fancier than Ian's second attempt.


Aladin

Posted by Fred Donahoe on August 15, 2001 7:37 AM

Assume your array is in Columns A:E, Insert a sixth column which will be "F" of your array. Put a formula in that column which returns the value of what is in the next row but 5th column Example:In cell F1 the formula will be =E2. replicate the formula. Your lookup ststement will be like this:
VLOOKUP("Shirley",A1:F16,6,FALSE)

The formula above is looking up Shirley in the array A1:F16 and is returning the value in the sixth column and is finding san exact match. The value in column six is the value in Column E and the next row.

Posted by Aladin Akyurek on August 15, 2001 3:48 PM

Index + Match

Fred,

Have a look at the data at 26861.html

The formula

=INDEX(A2:E12,MATCH(H4,$A$2:$A$12,0)+1,MATCH("Amount",$A$1:$E$1,0))

or the shorter version

=INDEX(A1:M12,MATCH(H4,A1:A12,0)+1,5)

will do the job hassle-free.

H4 is the cell that contains the Proj# of interest for which expenses must be retrieved.

Aladin