# Combine multiple IF statement/condition into one Google Sheet column

How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below: There will be 2 different vendor based on the model and size, which user can made selection on B7 to B9.
The formula in B8 is `=IF(AND(B7="S",B9<>3),"A1","B1")`

The question is how can I combine all the possible IF condition into column B8.

`IF(AND(B7="S",B9<>3),"A1","B1") + IF(AND(B7="E",B9<>5),"A2","B2") + IF(AND(B7="X",B9<>7),"A3","B3") + IF(AND(B7="Y",B9<>9),"A4","B4")`

Looking forward from the reply and really appreciated.

## 1 Answer

If it were up to me, I would not set up your sheet the way it is. But as you have things set up, try this formula in B8:

`=ArrayFormula(IFERROR(VLOOKUP(B7&B9,{A2:A5&C2:C5,B2:B5;A2:A5&E2:E5,D2:D5},2,FALSE),"NO MATCH"))`

The formula uses curly brackets to form a virtual array with the A2:A5 values concatenated with C2:C5, and to the right of that (in memory), the values from B2:B5. Underneath this array (as denoted by the semicolon), another array is formed with the A2:A5 values concatenated with the E2:E5 values this time, and to the right of that (in memory), the values from D2:D5. In the end, this array will contain just two columns: the first with the concatenations of every rows model with each possible vendor for that model, the second with the correlating size for that pairing.

The `VLOOKUP` then concatenates your selected model and size from B7 and B9 and looks for it in the virtual array above. If that combination is found, the value in column 2 of the virtual array will be returned. If it is not, the `IFERROR` will return "NO MATCH."

The `FALSE` in the `VLOOKUP` means "My results aren't in perfect ascending order and I'm looking for an exact match only."

• Hi Erik, Thank you, the above solution did solved my request. Really appreciated. However, do you mind to share how you going to setup your way in the sheet ? Thanks – Ask Feb 26 at 5:32
• Share a link to the sample spreadsheet here, and I will show you. – Erik Tyler Feb 26 at 8:00
• Hi Erik, please refer the sample link below: docs.google.com/spreadsheets/d/… – Ask Feb 26 at 8:19
• See the three sheets (tabs highlighted green) that I've added to your sample spreadsheet. These three new sheets work in tandem with one another and separately from your original Sheet1. I recommend studying the formulas in all three sheets, as well as the Data Validation applied to Search!C2 and Search!C3. Note how the Variables!B:B list changes as you make different selections in Search!C2 (which serves to limit the dropdown options available in Search!C3). I'll leave it to you to take it from here, explore and figure out how it all works together. – Erik Tyler Feb 26 at 16:58
• Hi Erik, thank you for showing me your prefer method. I will look into it, really appreciate for your time and great effort. Thank you :> – Ask Mar 2 at 8:14