Combining Vlookup and IFS

[ChrisLane]

Good afternoon. I have two tables as follows:
Table 1 has a client's name, and pack mode. The pack mode can be any of three items (FCL, LCL or LSE)
table two has two columns, one for pack mode and one for the related price. Each pack mode has its own price.

I need to do a vlookup in table 1 which will find the client name, and the related pack type. then, as part of the vlookup, I need a variable that will lookup the pack type in the second table and return the related price.

To do this, I am trying to combine Vlookup and IFS, and have derived the following formula.

=VLOOKUP(E6,'PM_Client_Matrix - New'!$B$1:$H$1276,IFS(K6="FCL",VLOOKUP([Package Type],'PM_Client_Matrix - New'!G1:H1276,2,FALSE),K6="LCL",VLOOKUP([Package Type],'PM_Client_Matrix - New'!G1:H1276,2,FALSE),K6="LSE",VLOOKUP([Package Type],'PM_Client_Matrix - New'!G1:H1276,2,FALSE)))


The formula returns a #N/A error. i have checked that the client in e6 appears in column B in table 2, and that the package type is in column B in table 2. I tried OR instead of IFS, to no avail.

Can somebody spot my error, please?

Kind regards

Chris

 

[ChrisLane]

My apologies, it returns a #Value error

 

[Fluff]

Maybe something along the lines of

Excel 2013/2016
	A	B	C	D	E	F
1	Name	Pack
2	Tom Pearce	FCL			Bill Brewer	30
3	Bill Brewer	FSE
4	Jan Stewer	LCL
5	Peter Gurney	FSE
6	Peter Davy	FCL
7	Dan'l Whiddon	FSE
8	Harry Hawke	LCL
9
10
11
12	Pack	price
13	FCL	10
14	LCL	20
15	FSE	30
Sheet1

Cell Formulas
Range	Formula
F2	=INDEX(B13:B15,MATCH(INDEX(B2:B8,MATCH(E2,A2:A8,0)),A13:A15,0))

 

[ChrisLane]

incidentally, the client in table 1 has three pack types in table two, as follows, which are all included in the IFS formula.

[TABLE="width: 394"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]CLIENT[/TD]
[TD]Packing Mode[/TD]
[TD]Insured Rate[/TD]
[/TR]
[TR]
[TD]GIRLY THINGS (PTY) LTD[/TD]
[TD]LSE[/TD]
[TD="align: right"]0.375[/TD]
[/TR]
[TR]
[TD]GIRLY THINGS (PTY) LTD[/TD]
[TD]FCL[/TD]
[TD="align: right"]0.250[/TD]
[/TR]
[TR]
[TD]GIRLY THINGS (PTY) LTD[/TD]
[TD]LCL[/TD]
[TD="align: right"]0.125[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Can you post examples of all three tables.
If the client has all three pack types, why not just lookup the pack type?

 

[ChrisLane]

What I am trying to is, match the client and pack type in table 1, to the client and pack type in table 2, and then return the insured rate from table 2. there are multiple clients, and each client cn have multiple pack types, and insured percentages. So, by inserting the formula in table 1, I am wanting to know the insured percentage for each combination of client and pack type.

 

[Fluff]

Can you please post examples of all three tables, along with expected outcome.

 

[ChrisLane]

Thanks Fluff. i understand the formula that you posted, just trying to get it to work in my workbook, with the different tables etc. i have split the formula into 2, and each one works, but the formula as a whole doesn't. What I mean is that the outer index function works, as does the inner index, but I'm doing something wrong when I combine the two - I get an error saying I have two few arguments. This what I have derived

=INDEX('PM_Client_Matrix - New'!H:H,MATCH(INDEX('PM_Client_Matrix - New'!H:H,(MATCH(E:E,'PM_Client_Matrix - New'!B:B,0)),'PM_Client_Matrix - New'!G:G,0))

Column E is from table 1
PM_Client_Matrix - New is table 2.

The formula is put into a separate column in table 1- which table is actually called Charge Code Report

 

[ChrisLane]

Sorry, Fluff, I did se your request to post examples of the tables. Can I just copy and paste into the message box?

 

[Fluff]

The best way is to use one of the add-ins available here https://www.mrexcel.com/forum/about-board/508133-attachments.html