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!

Location	Opening	Receipts	Closing	Cost
4911	14 682.32	33 180.48	12 366.11	35 496.69
Apples	556.96	0	169.93	387.03
Oranges	531.41	1 987.39	621.28	1 897.52
Lemons	1 804.82	2 680.03	4 315.13	169.72
Pears	10 374.18	26 257.50	6 362.33	30 269.34
Plums	0	0	0	0
Beverage	1 414.95	2 255.56	897.43	2 773.08

<tbody>
</tbody>

 

[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.