VLookup based on multiple criteria possible? | General Excel Questions & Answers

VLookup based on multiple criteria possible?

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

April 12, 2017 - 11:47 pm

Can't figure it out. See attachment, where in yellow also some explanation.

More rows in an Excel-table with sometimes the same information, but Always a third item that's different.

I need the info from another column, based on the first three columns.

Don't now if it's possible with either Vlookup or Index/Match or combination?

Thanks for helping in advance!

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

April 13, 2017 - 1:55 am

Hi Hans

See if this is what you are looking for. There are two suggestions.

Suggestion (1) I made use of the row numbers that you already extracted in columns O,P and Q to get the values required.

Suggestion (2) I created a Helper Column G and then do a lookup using INDEX/MATCH on that column to get the values required.

Suggestion(2) is the best and the easiest. If you need to use VLOOKUP instead, then the Helper Column must be in column A.

Hope this helps.

Sunny

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

April 13, 2017 - 10:43 am

Hi Frans

Sorry I attached the wrong file in the previous post.

Please refer this one.

I have added a 3rd method for you.

Cheers

Sunny

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

April 13, 2017 - 9:24 pm

Hi Sunny,

You're the man again! I downloaded earlier today, but due to strange behaviour of the website here on te workplace I couldn't reply at that moment. Your third solution is exactly what I was aiming at!

This helps a lot in a tricky difficulty.

Thanks very much! I owe you another one...... 😉

Frans

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

April 13, 2017 - 10:02 pm

Two questions now I'm working with this solution nr. 3.

First I see it's an array formula and it works perfect (luckily I learned here at the MOTH how this works). BUT: why is this the solution? Is that easy to explain why you have to use such an array? Is that because of the multiple criteria? I never would have thought in this direction, maybe because I don't understand the working of an array well.

Other one: I try to refer to the headers in the named tables. Is it possible to place the same name in a field and then refer to that field so the formula will look in the named table for that header?

Say I want to look at Table1[Kostenplaats], is it possible to place the word 'Kostenplaats' for instance in cel A1 and then do something like Table1["A1"]?

I tried several ways, but couldn't find a working solution. So maybe it's not possible, or I don't use the right way to descripe it?

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

April 14, 2017 - 2:25 am

Hi Frans

Thanks for your compliment Embarassed I will try to help out if I am able to.

Answer 1: It is a bit difficult for me to explain ARRAY formulas as I am not very familiar with them. So far I am lucky to be able to change many of such ARRAY formulas that I find to suit my needs (and yours as well).

Answer 2: I believe you wanted the INDIRECT function. See if the attachment is what you are looking for.

Sunny

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

April 14, 2017 - 4:52 am

Hi Frans,

I think you want the "Full" INDEX function, to detect the row where the match is found, but also the column of the table where to take data from.

You can try this formula in cell P8 of your sample file, and you can drag it to the right and down:

=INDEX(TBformatie2,AGGREGATE(15,6,ROW(TBformatie2[Kostenplaats])/((TBformatie2[[Kostenplaats]:[Kostenplaats]]=$H8)*(TBformatie2[[Omschrijving kostenplaats]:[Omschrijving kostenplaats]]=$I8)*(TBformatie2[[functie]:[functie]]=$J8)),1)-ROW(TBformatie2[[#Headers],[Kostenplaats]]),MATCH(P$7,TBformatie2[#Headers],0))

AGGREGATE is more versatile, I used the SMALL function (15 represents the SMALL function in the first argument of the AGGREGATE function), but you can also use option 14, which is the LARGE function. The MATCH function will always return the first match, in case of duplicate matches, with AGGREGATE you can return the first match, or the last match, or any match between, for example, in the above formula, if you change the red number to 2, it will return the second match.

To make table references absolute, they need to be duplicated, as in the above formula (for example [[functie]:[functie]]), otherwise the reference will change when you drag the formula to the right. There is an article about absolute references in tables on our site, you can read more there.

Sunny's formula:

=INDEX(TBformatie2[overhead BBV], MATCH($H8&$I8&$J8,TBformatie2[Kostenplaats]&TBformatie2[Omschrijving kostenplaats]&TBformatie2[functie],0))

is indeed an array formula because it is joining 3 columns in the lookup_array argument of the Match function and they need to be joined at row level. Basically, an array formula will evaluate the ranges row by row, performing the specified operations at row level. A normal formula will not perform operations at row level, it will simply evaluate the entire range.

AGGREGATE, and SUMPRODUCT, are native array functions, which means that they do not need to be entered with CSE, they are already designed to perform operations at row level.

Hope this will clarify a few things for you.

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

April 14, 2017 - 6:19 am

Thanks both Sunny and Catalin!

This helps a lot. I need to figure out the working of your suggestion Catalin and see what it exactly does. Difficulty for me is that I sometimes more understand the English way of doing with formulas, but get lost with the dutch translation 🙂

It's always nice to see how Excel translate this things itself!

I also have a look at your indirect solution Sunny. Now I read it (didn't see the file at this moment), I almost am sure this is the solution I read about somewhere here or on another Excel site.

Thanks again, this helps a lot!

Frans