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

Gauranga Published at Dev

Gauranga

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.)

enter image description here

sancho.s ReinstateMonicaCellio

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.

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-6

Comments

0 comments

From Dev

VBA/Excel macro - trying to simplify code using an array that draws from one worksheet and populates across multiple worksheets

From Dev

Related Related

Article