Offset, VHookup - Confused

[Guest]

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?

 

[Guest]

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:

 

[Bondi]

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

 

[Don Guillett]

try using match to find the column and use that within an index formula
=INDEX(A2:X3,2,MATCH(C1,2:2)+1)

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

I'm so sorry! I just realized I wrote what I was looking for wrong. Please
see my next post.

Again, my appologies.