CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
Emm
Emm

Reputation: 2507

If blank return 0 else run vlookup

I would like to use the vlookup function to match two criteria values firstly based on the value selected in a dropdown menu (country) and the value in A2(name). If the value in A2 Sheet matches the one of the values in the A column in Sheet2 and the value of the dropdown menu in Sheet1 matches one of the values in Sheet2 Column D (Which is a concatenation of the name and country) I would like to return the corresponding value in Sheet2 ColumnC.

If the value is 0 or blank I would like to return 0.

This is what I have tried

=ARRAYFORMULA(
 IF(
   ISBLANK(
    IFERROR(VLOOKUP(A2&C2,Sheet2!$A$2:$E$61,3,1),"0"))))

Not sure what I might be doing wrong

Here is a sample of my data

Sheet 1:

A           B             C        
name1      (vlookup)    [dropdownmenu]

and Sheet 2

A                 B                    C
name1            val                   concatenationofA&B

Here is a test sheet as requested:

https://docs.google.com/spreadsheets/d/1jsFnaGY7N9nXyPs5vR32jG5G838w1SgB2XIad7bEFXg/edit?usp=sharing

Upvotes: 1

Views: 109

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&C2:C, {Sheet2!A2:A&Sheet2!B2:B, Sheet2!C2:C}, 2, 0), 0))

enter image description here

Upvotes: 2

Emm
Emm

Reputation: 2507

I managed to resolve this using the query function

=QUERY(Sheet2!$A$2:$E$61,"select C where B = """&C12&""" and A = """&A12&""" ")

The only problem is I don't know how to suppress NA values/replace them with the value 0

Upvotes: 0

Related Questions