need help using offset and vlookup functions please

[mrexcel57]

I use Vlookup to get info from, e.g., B35 in a list B10:AR147
VLOOKUP("tom",distribut!$B$10:$AR$147,23,FALSE)

I want also to get info from cell B34 and B36
I used Hlookup but my list is random, has blank cells, not in order and no heading
I tried using
=OFFSET(VLOOKUP("tom",distribut!$B$10:$B$147,1,FALSE)),-1,0)
but did not work

 

[Jerry Sullivan]

Instead, try using MATCH to the find the index number of "tom" within the column or range.
Then use INDEX to find the actual Values in Cells (B34,B35,B36) using the index number-1, and +1.

 

[mrexcel57]

Would you kindly write the formulae using MATCH and INDEX please?
Thank you

 

[Jerry Sullivan]

Would you kindly write the formulae using MATCH and INDEX please?

Based on your screen shot, and assuming your data runs from Row 2:500,
you could find the row within the data range that "tom" appears,
and then use that to find the values above and below it in Col C.

Excel Workbook
	D	E
1	Match at which Row in Col C?	34
2	Value at Match Cell?	tom
3	Value above Match Cell?	191
4	Value below Match Cell?	2008
Match-Index

 

[Jerry Sullivan]

mrexcel57, Sending you a follow up, just in case you opened my post before I made some edits. Please read the revised post that shows the formula for Cell E1.

 

[mrexcel57]

I am very grateful for your help
I wish all the best
Thank you very much

 

[Jerry Sullivan]

You're welcome!

Just to clarify my poor description, Match returns "the relative position of an item in an array".

So, it's not actually returning the Row within the Worksheet, but rather the item number within the specified range. That's why Match returns 34 instead of 35 in my example.

Cheers.