Index Match / Search / Find not working

[BobJApples]

Good Morning All,

I am trying to use an index match/search/find but none of what i am trying to do is working. Workbook attached.

Basically in this demo sheet, I am trying to find the cost of "Pears". But not "Apple and Pears" or "Pears in Syrup".

Indexs are setup the way they are because in my real spreadsheet, these are appearing on another sheet and are set up dynamically.

How do i find the cost of pears? Thank you

Bob

[davsth]

will you be using filters, and can values appear twice?

=SUMIF(B3:B10,"Pears",C3:C10) if no repetition

=INDEX(C3:C10,MATCH("Pears",B3:B10,0)) first match

last match
=LOOKUP(2,1/("Pears"=B3:B10),C3:C10)

what do you want to achieve?

[BobJApples]

Hello,

Thank you, the first one works just fine thankyou, however i can not use the "b3:b10" part because my area and size constantly change depending on what is pasted into it.

For instance, i may have to look in column B for the search and need the result out of column E but for the next data set which gets pasted in I may be column A and column H. I can determine these columns dynamically, but i need to pass them through to the sumif.

Hence my use of using "INDEX(B:C,3,2):INDEX(B:C,10,2)". Is there a way to achieve this with your solution?

Thank you

[BobJApples]

Hello,
I was eventually able to solve this with this formula:

=SUMIF(INDEX(B:C,3,1):INDEX(B:C,10,1),"Pears",INDEX(B:C,3,2):INDEX(B:C,10,2))

Thank you