Match to return value of adjacent cell

[rfinnegan]

Hi All:

I have a row of data from C21 to N21. I want to search C21 - L21 for what is in N21, then return the data that is in the cell to the right of the cell that matches N21.

For example in N21 I have $9.40, I want to search C21-L21 for $9.40. I can see $9.40 is in cell F21 and I want the formaula to return what is in G21 ($320.92).

I've tried 'Match' but it's not working. I tried 'Offset' too, but that didn't work either. Chances are they do work, just not the way I wrote them.

By the way, I'm on Excel 2003.

If it helps, the reason I'm doing this is to compare how the average highest price per item (in this case, $9.40) relates to the highest daily sales of that item (in this case NOT $320.92).

The data is arranged in pairs, with the average price listed next to the average daily sales for each person. For example C21 has the average price Jack charged and D21 has the daily sales for Jack. E21 has Jills average price and F21 has her daily salels, etc.

N21 is displaying the results of this statement "=MAX(C21,F21,I21,L21)", which identified the highest average price (again, $9.40). I have the highest daily sales from this statement "=MAX(D21,G21,J21,M21)" in this case $321.30.

Obviously the highest average price did not result in the highest daily sales; I want to show this, as well as the 'cost' of over & under pricing.

As always, thanks in advance.

 

[Matty]

I have a row of data from C21 to N21. I want to search C21 - L21 for what is in N21, then return the data that is in the cell to the right of the cell that matches N21.

For this, try:

=INDEX(C21:L21,MATCH(N21,C21:L21,0)+1)

Not sure I follow the rest. Posting an example we can copy - as well as the expected result - will help us understand.

Matty

 

[rfinnegan]

Thanks Matty. You nailed it.

 

[Matty]

Thanks Matty. You nailed it.

No problem. Happy to help.

Matty