An interesting twist on VLOOKUP (multi-conditional)

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

An interesting twist on VLOOKUP (multi-conditional)

Post by Rudi »

A good article on Chandoo's site (from Sohail Anwar) illustrating an interesting way to convert a VLOOKUP into a multi-conditional version.

Nice one Sohail... :thumbup:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: An interesting twist on VLOOKUP (multi-conditional)

Post by rory »

I guess the author never heard of INDEX/MATCH... ;)
Regards,
Rory

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: An interesting twist on VLOOKUP (multi-conditional)

Post by HansV »

One other remark: while array formulas are very powerful, they are often calculation-intensive. An auxiliary column may help reducing the number of calculations, and they also may make formulas more readable.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: An interesting twist on VLOOKUP (multi-conditional)

Post by Rudi »

rory wrote:I guess the author never heard of INDEX/MATCH... ;)
Hi guys,

This is not a challenge, but I'm just interested to know if the INDEX/MATCH version would be as flexible as the Array version in the OP?
The article illustrates that the formula can handle name and surname or surname and name etc... How do I set up an INDEX/MATCH to locate a specific customer similar to the ARRAY version?

TX
Example INDEX-MATCH.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: An interesting twist on VLOOKUP (multi-conditional)

Post by HansV »

In row 13, as an array formula: =INDEX($A$2:$A$10,MATCH(B13,$B$2:$B$10&" "&$C$2:$C$10,0))

In row 17, as an array formula: =INDEX($A$2:$A$10,MATCH(B17,$C$2:$C$10&", "&$B$2:$B$10,0))
Example INDEX-MATCH.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: An interesting twist on VLOOKUP (multi-conditional)

Post by rory »

Or if you don't want to have to array-enter:
=INDEX($A$2:$A$10,MATCH(B13,INDEX($B$2:$B$10&" "&$C$2:$C$10,),0))
and
=INDEX($A$2:$A$10,MATCH(B17,INDEX($C$2:$C$10&", "&$B$2:$B$10,),0))
Regards,
Rory

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: An interesting twist on VLOOKUP (multi-conditional)

Post by Rudi »

TX both...
Appreciate the share! :cheers:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.