Excel 2007 - INDEX/MATCH/VLOOKUP Using Multiple Searches or Wildcards

[greengo204]

Hi,

Could someone please assist me with the below:

I have two tables

1.) Table A - Which contains data that changes regularly
2.) Mapping Table - Which has a static table that will hold static data used in a lookup formula to allocate segments (groups) to table A

My end goal is have a formula that returns a values if [GL, Entry Contains, Entry Does Not Contain] are found on the same row within there columns. It is possible not to have any matches, purpose is only to return a a value if it matches the criteria.

Only the GL field will be an exact match, while 'Entry Contains' &'Entry Does Not Contain' are free text fields which will need to be searched.

Mapping Table

Segment | GL     |Entry Contains  | Entry Does Not Contain
A       | 100000 |Transaction     | 
B       | 100001 |Debtors         | System
D       | 100002 |Payables        |
E       | 100003 |                | Alpha

Table A

GL    | Entry           | Net
100000| Transaction     | 1000
100001| Debtors         | 2000  
100001| System - Debtors| 3000
100003|                 | 4000 
100003| Alpha           | 6000
100000|                 | 5000
100005|                 | 7000

Expected

GL    | Entry           | Net   | Segment
100000| Transaction     | 1000  | A
100001| Debtors         | 2000  | B
100001| System - Debtors| 3000  | 
100003|                 | 4000  | E
100003| Alpha           | 6000  |     
100000|                 | 5000  |
100005|                 | 7000  |

I have been attempting this using VLOOKUP, INDEX, MATCH, SEARCH, WILDCARDS and have been unsuccessful.

Thanks in advance for any help!!

Kyle

 

[SkipVought]

hi,

Please post one of more formulas that has been unsucessful and explain what result you got and what result you expect.

FYI, complex criteria and returning string values, rather than aggregations, is best handles by tools like PivotTable and MS Query. faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

I can sorta get what you want by moving the Segment Column to the column after "Entry Does Not Contain"

=IF(VLOOKUP(A9,B1:E5,4,FALSE)=VLOOKUP(B9,C1:E5,3,FALSE),VLOOKUP(A9,B1:E5,4,FALSE))

The only problem with this code is that you need something for Segment E (e.g., a space) otherwise a blank cell won't work. You really don't need a "Entry Does Not Contain" column since the VLOOKUP is doing an exact match.

 

[SkipVought]

You would use the lookup for that value.

If you get a #NA! then it does NOT CONTAIN, otherwise it contains.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]