| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup cell 1 below another cell
I'm trying to add a formula that will return the cell directly below the cell
that my VLOOKUP formula pulled from. Any suggestions? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup cell 1 below another cell
=match(a1,sheet2!a:a,0)
will give you the row where A1 matches the value in sheet2 column A. =match(a1,sheet2!a:a,0)+1 will give you the row under the match. So =index(sheet2!a:a,match(a1,sheet2!a:a,0)+1) should return the value under that match. (as long as there is a match and it's not in the last row!) Nate wrote: I'm trying to add a formula that will return the cell directly below the cell that my VLOOKUP formula pulled from. Any suggestions? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup cell 1 below another cell
For some reason everytime I try the match formula it returns the #N/A error.
My vlookup is VLOOKUP(A2, Mfg!C2:L22695, 3, FALSE). When I write the match formula I'm using MATCH(B2, Mfg!C2:L22695, 0) but that alone returns an error. B2 is the cell that contains the vlookup. I've tried the match formula just using the cell that was my lookup value for the vlookup, but even that returns an error. Every lookup value I'm using has data in the table array. Any idea what I'm doing wrong? Thanks for all your input. "Dave Peterson" wrote: =match(a1,sheet2!a:a,0) will give you the row where A1 matches the value in sheet2 column A. =match(a1,sheet2!a:a,0)+1 will give you the row under the match. So =index(sheet2!a:a,match(a1,sheet2!a:a,0)+1) should return the value under that match. (as long as there is a match and it's not in the last row!) Nate wrote: I'm trying to add a formula that will return the cell directly below the cell that my VLOOKUP formula pulled from. Any suggestions? -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup cell 1 below another cell
My vlookup is VLOOKUP(A2, Mfg!C2:L22695, 3, FALSE).
When I write the match formula I'm using MATCH(B2,Mfg!C2:L22695, 0) but that alone returns an error The MATCH lookup_array must be a 1 dimensional array. That is, a single row or single column. =VLOOKUP(A2, Mfg!C2:L22695, 3, FALSE) =INDEX(Mfg!E2:E22695,MATCH(A2,Mfg!C2:C22695,0)+1) -- Biff Microsoft Excel MVP "Nate" wrote in message ... For some reason everytime I try the match formula it returns the #N/A error. My vlookup is VLOOKUP(A2, Mfg!C2:L22695, 3, FALSE). When I write the match formula I'm using MATCH(B2, Mfg!C2:L22695, 0) but that alone returns an error. B2 is the cell that contains the vlookup. I've tried the match formula just using the cell that was my lookup value for the vlookup, but even that returns an error. Every lookup value I'm using has data in the table array. Any idea what I'm doing wrong? Thanks for all your input. "Dave Peterson" wrote: =match(a1,sheet2!a:a,0) will give you the row where A1 matches the value in sheet2 column A. =match(a1,sheet2!a:a,0)+1 will give you the row under the match. So =index(sheet2!a:a,match(a1,sheet2!a:a,0)+1) should return the value under that match. (as long as there is a match and it's not in the last row!) Nate wrote: I'm trying to add a formula that will return the cell directly below the cell that my VLOOKUP formula pulled from. Any suggestions? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup cell 1 below another cell
If your VLOOKUP formula was something like this:
=VLOOKPU(A1,A2:B100,2,0) Then try something like this: =INDEX(B2:B100,MATCH(A1,A2:A100,0)+1) -- Biff Microsoft Excel MVP "Nate" wrote in message ... I'm trying to add a formula that will return the cell directly below the cell that my VLOOKUP formula pulled from. Any suggestions? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup cell 1 below another cell
Sorry. I wasn't familiar with the match formula at first. This is exactly
what I was looking for. Thanks! "T. Valko" wrote: If your VLOOKUP formula was something like this: =VLOOKPU(A1,A2:B100,2,0) Then try something like this: =INDEX(B2:B100,MATCH(A1,A2:A100,0)+1) -- Biff Microsoft Excel MVP "Nate" wrote in message ... I'm trying to add a formula that will return the cell directly below the cell that my VLOOKUP formula pulled from. Any suggestions? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup cell 1 below another cell
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Nate" wrote in message ... Sorry. I wasn't familiar with the match formula at first. This is exactly what I was looking for. Thanks! "T. Valko" wrote: If your VLOOKUP formula was something like this: =VLOOKPU(A1,A2:B100,2,0) Then try something like this: =INDEX(B2:B100,MATCH(A1,A2:A100,0)+1) -- Biff Microsoft Excel MVP "Nate" wrote in message ... I'm trying to add a formula that will return the cell directly below the cell that my VLOOKUP formula pulled from. Any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Show a blank result in a cell when there is no value in the "Lookup" cell | New Users to Excel | |||
| Need to lookup value in cell, dependent on value in another cell | Excel Worksheet Functions | |||
| formatting a cell the same as the source cell from a lookup table | Excel Discussion (Misc queries) | |||
| Lookup and Sum in same cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
