Searching an excel with two search words (multiple lookup) using vba macro

Question

I am using vba macros.
Task:
1) User will give two inputs viz. number of devices sold (column B) and week number (column A).
2) Search the excel file using these two and get the earnings.

(Conditions:
1. We dont have to modify anything in the excel file.
2.And we have to first search devices sold and then we can go to week number.)

Note: Actually i have a different problem, this is generalization for that.

I used find function but I dont understand why its working.

Please tell what is to be done.


Consider the input is 11 and week 3 (so search for 11 and then check if its corresponds to week 3 or not. If not, move to next. I tried this but ended up in an infinite loop.)

Answer 1

You are looking for a Lookup with multiple criteria. A quick search gives several useful links, listed below. I have marked with * those that I have answered. I have filtered out some of the links that actually deal with other issues.

As seen in the links, there are several options to achieve your goal. I personally prefer those that do not need array formulas, using an answer to question 1 with LOOKUP or an answer to question 2 with SUMPRODUCT.

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

1. Multiple Column vs Multiple Column Lookup *

2. Multiple-column based lookups in Excel *

3. Lookup Database (Multiple criteria)

4. Excel Lookup with multiple queries

5. Multiple VLOOKUP LOOKUP IF Statement?

6. Returning a value if three columns match in excel *

7. VLookup multiple columns

8. Comparing two columns in one Excel sheet, to two columns in another sheet, and if they match, copy data from another column *

9. Macro for vlookup with 2 vertical criteria *

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

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

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

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

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

PS: The (probably partial) list of (probably) duplicated questions provided above shows the usefulness of what was proposed in https://meta.stackexchange.com/questions/211366/group-duplicate-questions-for-convenience

Answer 2

You dont need any VBA for such a simple thing. Use this array formula:

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

Dont forget to enter the formula with Ctrl Shift Enter (it is an array formula)

Answer 3

Concatenate week and number of devices and then search like usual one keyword search.