find a value and then return the value based on another criteria

[cmacnab]

Hi all, hoping someone can help with this. I have a report that is not formatted to work with easily. I need to find the following (I hope this shows properly, I'm having problems inserting a table):

Cell A2 is the location number. I need a formula to return the value in cell D5 (lemons $4,315.13). I would need another to return the value in cell E6 (pears $30,269.34). I have several sets of data like this and the number of lines can vary by location. I believe there is a way to do this using a Vlookup I believe but I can't recall the correct formula.

Thank you![TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Opening[/TD]
[TD]Receipts[/TD]
[TD]Closing[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]4911[/TD]
[TD]14 682.32[/TD]
[TD]33 180.48[/TD]
[TD]12 366.11[/TD]
[TD]35 496.69[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]556.96[/TD]
[TD]0[/TD]
[TD]169.93[/TD]
[TD]387.03[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]531.41[/TD]
[TD]1 987.39[/TD]
[TD]621.28[/TD]
[TD]1 897.52[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD]1 804.82[/TD]
[TD]2 680.03[/TD]
[TD]4 315.13[/TD]
[TD]169.72[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]10 374.18[/TD]
[TD]26 257.50[/TD]
[TD]6 362.33[/TD]
[TD]30 269.34[/TD]
[/TR]
[TR]
[TD]Plums[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Beverage[/TD]
[TD]1 414.95[/TD]
[TD]2 255.56[/TD]
[TD]897.43[/TD]
[TD]2 773.08[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

how do you determine that you need D5 and/or E6?

 

[cmacnab]

I would be looking for either the closing or receipts total (2 different formulas but the answer should be the same for both) for a particular item; i.e. closing value for pears or receipts for lemons. It seems like it needs to be some type of double look-up.