I want to match the (Sheet1)clent /group to the (LookupTable)name /group and return the corresponding retention

[kellykivisto]

I want to match the (Sheet1) clent /group to the (LookupTable)name /group and return the corresponding retention.

Formula i am using in Sheet1 cell-Retention Policy but is returning the wrong retention because I have multiple instances of the client/ name??
=IF(ISNA(MATCH(A2&"/"&B2,LookupTable!A:A&"/"&LookupTable!B:B,0)),"",VLOOKUP(A2,LookupTable!$A$2:$H$100000,3,FALSE))

Problem:
It is only matching and inserting the first instance of the Retention when there are 3 possible answers.

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Group[/TD]
[TD]Retention Policy[/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]Ntwrkr_Daily 4 - 12:00 AM[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]NetworkDaily-6AM[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]Ntwrkr_Daily 4 - 12:00 AM[/TD]
[TD]Year[/TD]
[/TR]
</tbody>[/TABLE]








[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Group[/TD]
[TD]Retention Policy[/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]Ntwrkr_Daily 4 - 12:00 AM[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]NetworkDaily-6AM[/TD]
[TD]7 Years[/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]Ntwrkr_Daily 4 - 12:00 AM[/TD]
[TD]Month[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi and welcome to MrExcel,

Just returning the first instance of the Retention is caused by the resukt of the VLOOKUP function.

In wording your VLOOKUP does this:

1. take value of Cell A2 (potato)
2. Look in column A of the searchtable (LookupTable)
3. and if there is an exact match return the third column from the seachtable (LookupTable)

It will return the first exact match and then it's done, if it didn't find an exact match it will return #N/A and it's done.

So if you want it to return the 2nd or 3rd instance you need to instruct Excel to do so by adding criteria to your formula.
Of course these criteria must be set in a way Excel can distinguish if it needs to return the 1st, 2nd or 3rd instance.

eg: excel will never be able to distinguish the difference between Ntwrkr_Daily 4 - 12:00 AM from row 1 and 3 of your table. In the table they both will look as "potato\Ntwrkr_Daily 4 - 12:00 AM" hence Excel can't distinguish the differences but if you add the row number to, it will look like this "potato\Ntwrkr_Daily 4 - 12:00 AM\1" and "potato\Ntwrkr_Daily 4 - 12:00 AM\3".


HtH

 

[kellykivisto]

Thank you for your response!..that makes perfect sense, so how do I adjust the formula go through and look at each row?

Kelly

 

[jorismoerings]

Hi,

Can't answer that without knowing how you want to distinguish between row 1 and 3 of your example cause they are exactly the same.

 

[kellykivisto]

Sorry that was a typo...the groups should be:

[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Ntwrkr_Daily 4 - 12:00 AM[/TD]
[/TR]
[TR]
[TD]NetworkDaily-6AM[/TD]
[/TR]
[TR]
[TD]Networker - Yearly Backups[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi Kelly,

If i was just a typo and your list contains only unique items, your initial formula could suffice.
If you need more help, just share a example copy of your workbook.

 

[kellykivisto]

I'm sorry am not sure how to attach a workbook? The only entries are those that I have already posted though, will these not work?

 

[jorismoerings]

Hi Kelly,

Look at this:

Excel 2016 (Windows) 64 bit
	A	B	C
1	Client	Group	Retention Policy
2	potato	Ntwrkr_Daily 4 - 12:00 AM	Year
3	potato	NetworkDaily-6AM	7 Years
4	potato	Networker - Yearly Backups	Month
Sheet1

Cell Formulas
Range	Formula
C2	{=IFERROR(INDEX(LookUpTable!$C$2:$C$4,MATCH(1,(LookUpTable!$A$2:$A$4=Sheet1!A2)*(LookUpTable!$B$2:$B$4=Sheet1!B2),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kellykivisto]

Thank you so much, that worked perfectly! Now I just have to figure how to post the samples the way you have!!

Kelly

 

[jorismoerings]

Posting Aids
You cannot attach or upload actual files as samples.
However, you can post small Excel screen shots using MrExcel HTML Maker (Win), Forum Tools Add-In by RoryA (Win & Mac), Excel jeanie (Win) or Borders-Copy-Paste (Either). There is a little more help about some of these methods in this Attachments Sticky. Test the methods in the Test Here forum before use in a main forum for the first time.
If posting vba code, please use Code Tags - like this

Paste code here

- or use the VBHTML Maker
There is a Test Here forum on this board where you can test these posting aids before using them in your actual questions.

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html