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

benjennings

New Member
Joined
Mar 4, 2008
Messages
32
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

benjennings

New Member
Joined
Mar 4, 2008
Messages
32
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 :biggrin:.

Regards

Ben
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Maybe this:

Code:
=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
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316

ADVERTISEMENT

If you arrange your table this way in A1:C5
Code:
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:
Code:
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

New Member
Joined
Mar 4, 2008
Messages
32
Yes, thank you, this also works well and it's not too difficult to re-arrange the table.

Many thanks

Ben
 

benjennings

New Member
Joined
Mar 4, 2008
Messages
32

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
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 LookupVResult
Exact1111-000 241100A 1111-000241100A
Like1111-??? 241100B 1111-050241100C
Between1111-0001111-899241100C 1111-916241100B
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top