vlook up on multiple drop down lists

Occasional Contributor

vlook up on multiple drop down lists

SOLVED

-Please see the table 1 below to calculate cost in the Table 2 according to variable values in the cells ( K4,L4,M4 ) ,using vlookup formula

thanks!

7 Replies

Re: vlook up on multiple drop down lists

You say you prefer a VLOOKUP formula, but don't provide a reason.

May I recommend instead (if you have the most recent release of Excel) the FILTER function.

=FILTER(G7:G19,(C7:C19=K8)*(D7:D19=L8)*(E7:E19=M8),"No match")

Re: vlook up on multiple drop down lists

If you don't happen to have the most recent Excel release, if (in other words) that FILTER solution doesn't work, here's a solution that takes advantage of one of my favorite old-school functions that very few people know about...the various Database functions that begin with D_____. In this case DGET. It allows you to specify multiple criteria for retrieving a value from a talbe, exactly what you're trying to do. All you need to do to get it to work, is change the headings above the dropdowns where you specify the criteria so they match the column headings in Table1.

The formula is visible up there at the top of that image.

=IFERROR(DGET(B6:G19,"Cost",K7:M8),"No Match")

I've attached the revised file so you can experiment with other values to see how it works.

Re: vlook up on multiple drop down lists

Thanks @mathetes

I use Excel 2016  and I do not have FILTER function

Re: vlook up on multiple drop down lists

Thank you @mathetes  so much

that is really great

Re: vlook up on multiple drop down lists

Those D____ functions are very nice and useful. They've been around in Excel for decades, yet very few users are aware of them. And they're probably functionally superseded now by such functions as FILTER (which, yes, is newer than your version, but a good reason to update your software). FILTER can be used in conjunction with other functions to do lots of things, but so long as you don't have it, it's worthwhile to be aware of these long-available tools for data retrieval (database functions)

1. DGET, which will retrieve a value
2. DSUM, which will retrieve the sum of multiple rows that meet a given set of criteria
3. DAVERGE
4. well, let me just refer you to this list: https://www.excelfunctions.net/excel-database-functions.html

Re: vlook up on multiple drop down lists

@Ahmad920 , here's a variation using SUMIFS:

``=SUMIFS(\$G\$7:\$G\$19,\$C\$7:\$C\$19,K8,\$D\$7:\$D\$19,L8,\$E\$7:\$E\$19,M8)``

Re: vlook up on multiple drop down lists

well noted , Thanks