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:

enter image description here

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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.