Combining Vlookup and IFS

ChrisLane

New Member
Joined
May 22, 2014
Messages
38
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Maybe something along the lines of


Excel 2013/2016
ABCDEF
1NamePack
2Tom PearceFCLBill Brewer30
3Bill BrewerFSE
4Jan StewerLCL
5Peter GurneyFSE
6Peter DavyFCL
7Dan'l WhiddonFSE
8Harry HawkeLCL
9
10
11
12Packprice
13FCL10
14LCL20
15FSE30
Sheet1
Cell Formulas
RangeFormula
F2=INDEX(B13:B15,MATCH(INDEX(B2:B8,MATCH(E2,A2:A8,0)),A13:A15,0))
 
Upvote 0
incidentally, the client in table 1 has three pack types in table two, as follows, which are all included in the IFS formula.

CLIENTPacking ModeInsured Rate
GIRLY THINGS (PTY) LTDLSE0.375
GIRLY THINGS (PTY) LTDFCL0.250
GIRLY THINGS (PTY) LTDLCL0.125

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
Can you post examples of all three tables.
If the client has all three pack types, why not just lookup the pack type?
 
Upvote 0
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.
 
Upvote 0
Can you please post examples of all three tables, along with expected outcome.
 
Upvote 0
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
 
Upvote 0
Sorry, Fluff, I did se your request to post examples of the tables. Can I just copy and paste into the message box?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top