Lookup value in range but find exact/between/like??

[benjennings]

Hi

Within a system I have a table (mapping table) that contains the rules of looking up a value and maps to an account code. I'd need to replicate this in Excel but am having difficulties with the formulas.

My table is in 3 columns, like this:

WhereClauseType (Col A)	WhereClauseValue (Col B)	Target Account
Exact	1111-000	241100A
Like	1111-???	241100B
Between	1111-000,111-899	241100C

<tbody>
</tbody>

I need a formula that returns the Target Account by looking up a value from this table, however with a criteria that it looks for an "exact" match first, then a "between" match second, then lastly a "like" match.

For example:

lookup value 1111-000 returns 241100A
lookup value 1111-050 returns 241100C
lookup value 1111-916 returns 241100B
lookup value 1122-000 returns #N/A (as it doesn't fit in any criteria)

Many thanks

Ben

 

[benjennings]

Sorry, just realised was meant to put on the table above that Target Account is Col C. Also, if it helps to find a solution I could easily split the values for the "between", therefore end up with a table with 4 columns?

Many thanks to anyone that is clever enough to help me on this .

Regards

Ben

 

[markmzz]

Maybe this:

=IFERROR(IFERROR(VLOOKUP($E2,$B$2:$C$4,2,0),
LOOKUP(1,1/((LEFT($B$2:$B$4,FIND(",",$B$2:$B$4)-1)<=$E2)*(MID($B$2:$B$4,FIND(",",$B$2:$B$4)+1,999)>=$E2)),$C$2:$C$4)),
VLOOKUP(LEFT($E2,5)&"~???",$B$2:$C$4,2,0))

Markmzz

 

[benjennings]

Thanks, Brilliant, did the job perfect!!

Thank you.

Ben

 

[Ron Coderre]

If you arrange your table this way in A1:C5

WhereClauseType   WhereClauseValue  Target Account
Exact             1111-000          241100A
Between           1111-001          241100C
(blank)           1111-901          (blank)
Like              1111-???          241100B

E1: a value to analyze....1111-000

This regular formula assigns the correct target value:

F1: =IF(COUNTIF(E1,"1111-???"),CHOOSE(MATCH(E1,$B$2:$B$4,1),$C$2,$C$3,$C$5),"#N/A")

Is that something you can work with?

 

[benjennings]

Yes, thank you, this also works well and it's not too difficult to re-arrange the table.

Many thanks

Ben

 

[benjennings]

Sorry, Ron, I was wrong, it didn't work. It works on the example I gave, but this is only a sample of the mapping table, the actual mapping table is some 500 rows of data with various exacts, likes and betweens.

Many thanks

Ben

 

[Ron Coderre]

It's important to provide all of the rules and scenarios so we know what we're dealing with.
If there are that many variations, a User Defined Function created in VBA might be a better solution.

 

[markmzz]

Thanks, Brilliant, did the job perfect!!

Thank you.

Ben

You are welcome and I'm glad to help.

You can try this too:

Layout

WhereClauseType (Col A)	WhereClauseValue (Col B)	WhereClauseValue (Col C)	Target Account		LookupV	Result
Exact	1111-000		241100A		1111-000	241100A
Like	1111-???		241100B		1111-050	241100C
Between	1111-000	1111-899	241100C		1111-916	241100B
					1122-000
***********************	************************	************************	**************	**	*********	*********

<colgroup><col width="126" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4608;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;" span="2"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <tbody> </tbody>

Formula

In G2

=IFERROR(IFERROR(IFERROR(LOOKUP(1,1/($F2=$B$2:$D$614&$C$2:$C$614),$D$2:$D$614),
LOOKUP(1,1/(($B$2:$B$614<=$F2)*($C$2:$C$614>=$F2)),$D$2:$D$614)),
LOOKUP(1,1/(LEFT($F2,FIND("-",$F2))&"?"=LEFT($B$2:$B$614,FIND("-",$F2)+1)),$D$2:$D$614)),"")

Markmzz