Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, VHookup - Confused
Currently, I'm using Hlookup to find an employee's name, then return the
value in the cell directly below. However, I also need to get the value in the cell directly to the right of that value. For instance if I'm looking for John Doe and his name appears in cell b29, I need to return the values in B30 and B31. Here's what currently returns the valuein B30 =HLOOKUP(C5,'Jan 06'!B29:Y30,2,FALSE) I see several references to Index/Match/Indirect... I'm confused. How do I get the value in B31 in a new cell? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, VHookup - Confused
Try to use Vlookup,
so on B30 = Vlookup(b29,c29:d100,2,false) so on B31 = Vlookup(b29,c29:d100,3,false) hope this helps Regards from Brazil Marcelo "StephanieH" escreveu: Currently, I'm using Hlookup to find an employee's name, then return the value in the cell directly below. However, I also need to get the value in the cell directly to the right of that value. For instance if I'm looking for John Doe and his name appears in cell b29, I need to return the values in B30 and B31. Here's what currently returns the valuein B30 =HLOOKUP(C5,'Jan 06'!B29:Y30,2,FALSE) I see several references to Index/Match/Indirect... I'm confused. How do I get the value in B31 in a new cell? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, VHookup - Confused
StephanieH wrote: Currently, I'm using Hlookup to find an employee's name, then return the value in the cell directly below. However, I also need to get the value in the cell directly to the right of that value. For instance if I'm looking for John Doe and his name appears in cell b29, I need to return the values in B30 and B31. Here's what currently returns the valuein B30 =HLOOKUP(C5,'Jan 06'!B29:Y30,2,FALSE) I see several references to Index/Match/Indirect... I'm confused. How do I get the value in B31 in a new cell? Hi StephanieH, One way would be to use an array formula along the line of this: =TRANSPOSE(HLOOKUP(C5,'Jan 06'!B29:Y31,{2,3},FALSE)) If use this formula you should enter it in a cell then highlight the cell and the cell directly below it. The hit F2 and press Ctrl + Shift + Enter. The values from row 2 and 3 in your lookup array (B30 and B31) should now be on top of eachother. (If you want them horizontaly drop the Transpose and highlight the cell with the formula and the cell to the right of it) Regards, Bondi |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, VHookup - Confused
try using match to find the column and use that within an index formula
=INDEX(A2:X3,2,MATCH(C1,2:2)+1) -- Don Guillett SalesAid Software "StephanieH" wrote in message ... Currently, I'm using Hlookup to find an employee's name, then return the value in the cell directly below. However, I also need to get the value in the cell directly to the right of that value. For instance if I'm looking for John Doe and his name appears in cell b29, I need to return the values in B30 and B31. Here's what currently returns the valuein B30 =HLOOKUP(C5,'Jan 06'!B29:Y30,2,FALSE) I see several references to Index/Match/Indirect... I'm confused. How do I get the value in B31 in a new cell? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, VHookup - Confused
It has to be an Hlookup since I'm searching horizontally. Even with the
Hlookup 2nd row gives me the first number I'm looking for, but 3rd row would move down another row not over a column. "Marcelo" wrote: Try to use Vlookup, so on B30 = Vlookup(b29,c29:d100,2,false) so on B31 = Vlookup(b29,c29:d100,3,false) hope this helps Regards from Brazil Marcelo "StephanieH" escreveu: Currently, I'm using Hlookup to find an employee's name, then return the value in the cell directly below. However, I also need to get the value in the cell directly to the right of that value. For instance if I'm looking for John Doe and his name appears in cell b29, I need to return the values in B30 and B31. Here's what currently returns the valuein B30 =HLOOKUP(C5,'Jan 06'!B29:Y30,2,FALSE) I see several references to Index/Match/Indirect... I'm confused. How do I get the value in B31 in a new cell? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, VHookup - Confused
I'm so sorry folks. I just realized I wrote the cells wrong.
The names are in row 29 The numbers I need to return would next to each other on row 30. John Doe B29 I would pull the # in B30 (=HLOOKUP(C5,'Jan 06'!B29:Y30,2,FALSE) works here. But to get C30 is the part I'm having a hard time with. "StephanieH" wrote: Currently, I'm using Hlookup to find an employee's name, then return the value in the cell directly below. However, I also need to get the value in the cell directly to the right of that value. For instance if I'm looking for John Doe and his name appears in cell b29, I need to return the values in B30 and B31. Here's what currently returns the valuein B30 =HLOOKUP(C5,'Jan 06'!B29:Y30,2,FALSE) I see several references to Index/Match/Indirect... I'm confused. How do I get the value in B31 in a new cell? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, VHookup - Confused
I'm so sorry! I just realized I wrote what I was looking for wrong. Please
see my next post. Again, my appologies. "StephanieH" wrote: It has to be an Hlookup since I'm searching horizontally. Even with the Hlookup 2nd row gives me the first number I'm looking for, but 3rd row would move down another row not over a column. "Marcelo" wrote: Try to use Vlookup, so on B30 = Vlookup(b29,c29:d100,2,false) so on B31 = Vlookup(b29,c29:d100,3,false) hope this helps Regards from Brazil Marcelo "StephanieH" escreveu: Currently, I'm using Hlookup to find an employee's name, then return the value in the cell directly below. However, I also need to get the value in the cell directly to the right of that value. For instance if I'm looking for John Doe and his name appears in cell b29, I need to return the values in B30 and B31. Here's what currently returns the valuein B30 =HLOOKUP(C5,'Jan 06'!B29:Y30,2,FALSE) I see several references to Index/Match/Indirect... I'm confused. How do I get the value in B31 in a new cell? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Function | Excel Worksheet Functions | |||
Offset Function | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Question for use of offset and range | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions |