CALC: How to get cell contents from a range of cells?

[ChrisCrinkle]

I’m not very good at describing so lets start with an image. This is a simplified version of the problem I have:

Basically as you can see, from the “Check your diet” section, a user will type something in the “enter your food” box. The next line will produce a number for where in the array (A3 to A13) the MATCH is.

What I want is the F+G columns underneath that to have (in the middle, white boxes) to contain the text of that match, and the value next to it (in this case “Strawberries” and then “4” next to it). However obviously, if the user types in a different food, it will put the relevant data in the white cells. The grey cells above and below will be for the two entries above and below that one from the array.

Does anyone know what formula is needed? If it involves using the OFFSET function, please provide the full formula i would need to enter as I cannot figure that one out at all.

[Lupp]

If you specifically want to lookup the first column of a range you can use the shortcut function VLOOKUP() and won’t need MATCH() in combination with INDEX() or OFFSET().

(Images are mostly bad. Small example files are good.)
As I “read” your image:
Under “Fruit”: = G3
Under “Value”: =VLOOKUP($G$3;$A$3:$B$13;2;0)

[Read the help about VLOOKUP()].

[ChrisCrinkle]

Hi thanks that DOES return the exact contents for the white line. But what would I use for the two above and below rows which need to give the two lines of data above and below the exact match?