# Thread: Help needed - VLOOKUP based on IF statement, or similar Thanks: 0 Likes: 0

1. ## Help needed - VLOOKUP based on IF statement, or similar

Hi there,
I have a data set and Im trying to look up a specific value based on an initial look up in one column and then a secondary look up in another.

So data would look something like this:

A B C
Apples Granny Smyth 20
Apples Cooking 10
Apples Pink Lady 15

So based on the initial lookup of Apples, then a second lookup of Cooking to give me the total number.

Any help appreciated, thanks.

2. ## Re: Help needed - VLOOKUP based on IF statement, or similar

If you want a total sum or there is only one entry of each pair then:

=SUMIFS(C:C,A:A,"Apples",B:B,"Cooking")

Or if you just want the first hit:

=INDEX(\$C\$1:\$C\$100,MATCH(1,INDEX((\$A\$1:\$A\$100="Apples")*(\$B\$1:\$B\$100="Cooking"),0),0))

Or the last hit:

=LOOKUP(2,1/((\$A\$1:\$A\$100="Apples")*(\$B\$1:\$B\$100="Cooking")),\$C\$1:\$C\$100)

3. ## Re: Help needed - VLOOKUP based on IF statement, or similar

one way

ABC
1ApplesGranny Smyth20
2ApplesCooking10
3ApplesPink Lady15
4
5
6ApplesCooking10

Sheet14

Worksheet Formulas
CellFormula
C6=SUMPRODUCT(--(A1:A3=A6),--(B1:B3=B6),C1:C3)

4. ## Re: Help needed - VLOOKUP based on IF statement, or similar

=INDEX(C\$1:C\$3,MATCH(A6,IF(A\$1:A\$3=A\$5,B\$1:B\$3),0),1)
Array formula, use Ctrl-Shift-Enter

Where A5 is Apples
and A6 is Cooking

5. ## Re: Help needed - VLOOKUP based on IF statement, or similar

Something like
Excel 2013/2016
ABCDE
2ApplesGranny Smith20Apples
3ApplesCooking10Cooking
4ApplesBramley1510

Names

Array Formulas
CellFormula
E4{=INDEX(C2:C4,MATCH(E2&"|"&E3,A2:A4&"|"&B2:B4,0))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

6. ## Re: Help needed - VLOOKUP based on IF statement, or similar

Brilliant, thanks so much. Appreciate the help.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•