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...
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
