SOLVED

# Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

Copper Contributor

# Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

I have a excel spreadsheet and i need to lookup all itemcodes on worksheet "ChreosdataA", that have either "AFR" or "CCH" in column A:A, and have the words "Air Freshener" in column C. and display them in the worksheet "Corestock" in column A with no blank rows between them. Also the words are only partial matches as all codes are unique except for the prefix. I hope you can help please. David

5 Replies

# Re: Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

Attach a sample excel workbook and then show your desired output.

# Re: Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

=FILTER(ChreosdataA!A2:A1000, (ISNUMBER(SEARCH("AFR", ChreosdataA!A2:A1000))+ISNUMBER(SEARCH("CCH", ChreosdataA!A2:A1000)))*ISNUMBER(SEARCH("Air Freshener", ChreosdataA!C2:C1000)), "")

If you have more than 1000 rows of data, expand the ranges in the formula

# Re: Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

Hi.Thank you for the formula. However, i receive a #SPILL as a return

I have attached part of the spreadsheet that I am using with the relevant columns.

Thanking you

David

best response confirmed by davids4500 (Copper Contributor)
Solution

# Re: Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

Thanks! The formula should be entered in a single cell, and there should be enough empty cells below it for the result.

See the attached version.

# Re: Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

@HansVogelaar  Well this is so good. Worked perfectly and just what I needed.

Much appreciated and thank you for taking time.

David

1 best response

Accepted Solutions
best response confirmed by davids4500 (Copper Contributor)
Solution

# Re: Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

Thanks! The formula should be entered in a single cell, and there should be enough empty cells below it for the result.

See the attached version.