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...
An interesting twist on VLOOKUP (multi-conditional)
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
An interesting twist on VLOOKUP (multi-conditional)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: An interesting twist on VLOOKUP (multi-conditional)
I guess the author never heard of INDEX/MATCH... ;)
Regards,
Rory
Rory
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: An interesting twist on VLOOKUP (multi-conditional)
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: An interesting twist on VLOOKUP (multi-conditional)
Hi guys,rory wrote:I guess the author never heard of INDEX/MATCH... ;)
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: An interesting twist on VLOOKUP (multi-conditional)
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))
In row 17, as an array formula: =INDEX($A$2:$A$10,MATCH(B17,$C$2:$C$10&", "&$B$2:$B$10,0))
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: An interesting twist on VLOOKUP (multi-conditional)
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))
=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
Rory
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: An interesting twist on VLOOKUP (multi-conditional)
TX both...
Appreciate the share!
Appreciate the share!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.