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]

Hi Fluff. i have tried the apps referred to, but none of them work with my Excel 365/Windows 10. Further, I cannot figure out how to remove the Jeanie macro - I have tried to delete the code via the VB editor in the Personal workbook, but although I can see the macro in VB editor, I do not see how to delete it, and it is an autorun macro?. When I unhide the workbook, click on developer>macro the macro isn't listed. I am very frustrated as I need to post the screenshots of the worksheets, but the apps you refer to don't work in my Excel 365.
many thanks

 

[ChrisLane]

Hi Fluff. I managed to remove the macros, so not to worry. However, I am still battling with the Index formula you supplied. But, perseverance is my middle nane

 

[ChrisLane]

Hi Fluff.
I have being getting mixed results, have realised the shortcoming in your example. If i may explain

Your rows 1:8 are correct, except that Column C is where the formula sits to collect the premium % (where formula sits) - this would comprise table 1.

Table 2
Rows 13 onwards
Column A contains the client Name, Column B the pack Type and Column C the insured rate.

As mentionedin a previous post above, in table 2 the same client can have three different pack types, each with their own insured rate. So table two can consist of multiple rows of different clients, pack types and rates

So, the formula I am trying to derive looks in column A in table 1 for the client, tries to find the same client in table 2.
Then it will look for the Pack Type in column B in table 1, and try to find the pack type in table 2.
So, having found the correct combination of client and pack type, the related insured rate will by inserted in table 1, column C

So it is a double lookup to find the correct combination of client and pack type, to matvh the correct insured rate.

Kind regards

 

[Fluff]

More like this

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

Cell Formulas
Range	Formula
C2	{=INDEX($C$13:$C$21,MATCH(A2&B2,$A$13:$A$21&$B$13:$B$21,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ChrisLane]

Thanks Fluff. I'll give it a go now.
Kind regards
Chris

 

[ChrisLane]

Works perfectly, thanks Fluff.

 

[Fluff]

Glad to help & thanks for the feedback