Two-word excel search (multiple search) using vba macro

Question

I am using vba macros.
A task:
1) The user will provide two inputs: the number of devices sold (column B) and the week number (column A).
2) Find the excel file using these two and make a profit.

(Conditions:
1. We do not need to change anything in the excel file.
2.And we must first find devices sold , and then we can go to week number .)
Note: I actually have a different problem, this is a generalization for this.

I used the find function, but I do not understand why it works.

Please tell what to do.


Consider entering 11 and week 3 (so do a search of 11 and then check if it matches week 3 or not. If not, go to the next one. I tried this, but ended up in an infinite loop.)

Answer 1

You are looking for a search with several criteria . A quick search provides some useful links listed below. I am marked * by those to which I replied. I have filtered out some links that really relate to other issues.

As you can see from the links, there are several options to achieve your goal. I personally prefer those that do n't need array formulas , using the answer to question 1 with LOOKUP or the answer to question 2 with SUMPRODUCT .

Since you mentioned that you need a VBA solution, you can use WorksheetFunction to use the Excel formula in VBA code or check the answers to question 7.

• Multiple columns versus multiple column searches *

• Search multiple columns in Excel *

• Search Database (multiple criteria)

• Excel search with multiple queries

• Multiple VLOOKUP LOOKUP IF Statement?

• Return if three columns match excel *

• Multiple VLookup Columns

• Compare two columns in one Excel sheet with two columns in another sheet, and if they match, copy the data from another column *

• Macro for vlookup with two vertical criteria *

• http://spreadsheets.about.com/b/2014/01/08/excel-lookup-formula-using-multiple-criteria.htm

• http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list

• http://blogs.office.com/2012/04/26/using-multiple-criteria-in-excel-lookup-formulas/

• http://www.xl-central.com/lookup-multiple-criteria.html

• http://www.get-digital-help.com/2012/05/22/lookup-multiple-values-in-different-columns-and-return-a-single-value/

PS: the (possibly incomplete) list of (possibly) duplicate questions above shows the usefulness of what was suggested at https://meta.stackexchange.com/questions/211366/group-duplicate-questions-for-convenience

Answer 2

You do not need VBA for such a simple thing. Use this array formula:

 =INDEX(C2:C5;MATCH(1;(B2:B5=11)*(A2:A5="week 3");0)) 

Remember to enter the formula with Ctrl Shift Enter (this is an array formula)

Answer 3

Turn on the week and number of devices, and then search like a regular keyword search.