*DESPERATE* Need help immediately with vlookup

S

sixsteps268

New Member
Joined
May 9, 2013
Messages
15
Many many thanks in advance for what I've been told by other users, will be a big sucess in asking you experts for help! So needless to say I'm a first timer here on Mr. Excel.

What I need to do is return a value from a table based upon 3 variables. Variable 1 has 2 options, Variable 2 has 4 options, and Variable 3 has 9 options.

So my table has obviously 4 columns and I need to be able to return the value based upon the selection of variable 1, then by variable 2, and finally by variable 3. Does that make sense?

I've tried several things ranging from if and nested vlookups to a choose function and I can't seem to get it right.

Someone please help rescue me! This is for my boss and a big presentation!

Thanks!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
sixsteps268 said:
Many many thanks in advance for what I've been told by other users, will be a big sucess in asking you experts for help! So needless to say I'm a first timer here on Mr. Excel.

What I need to do is return a value from a table based upon 3 variables. Variable 1 has 2 options, Variable 2 has 4 options, and Variable 3 has 9 options.

So my table has obviously 4 columns and I need to be able to return the value based upon the selection of variable 1, then by variable 2, and finally by variable 3. Does that make sense?

I've tried several things ranging from if and nested vlookups to a choose function and I can't seem to get it right.

Someone please help rescue me! This is for my boss and a big presentation!

Thanks!
Click to expand...


More details would allow us to give you a much better answer.

With the information given i would suggest using nested Index,Match function to look up columns and rows for matches. but without more details, Values (atleast sample values) and Ranges (atleast sample ranges) it would be hard to give you much more advice for your question.
 
Upvote 0
jimmy90 said:
More details would allow us to give you a much better answer.

With the information given i would suggest using nested Index,Match function to look up columns and rows for matches. but without more details, Values (atleast sample values) and Ranges (atleast sample ranges) it would be hard to give you much more advice for your question.
Click to expand...

Sure, let me see if I can be more specific for you.

Here's my table. I need to return the value in the amount column. The facility, LOC, and Insurance values are in a validation drop down field. Does that make more sense? So I need a formula to be able to return the value after selecting a facility, LOC, and Insurance Provider.
Facility LOCInsuranceAmount
LPDTXCigna750
LPDTXBCBS572
LPDTXCompsych901
LPDTXGM/Cigna600
LPDTXValue Opt
LPDTXOON1777
LPRTCCigna525
LPRTCBCBS478
LPRTCCompsych519
LPRTCGM/Cigna600
LPRTCValue Opt450
LPRTCOON1456
LPPHPCigna395
LPPHPBCBS264
LPPHPCompsych641
LPPHPGM/Cigna300
LPPHPValue Opt340
LPPHPOON1147
LPIOPCigna275
LPIOPBCBS123
LPIOPCompsych254
LPIOPGM/Cigna130
LPIOPValue Opt206
LPIOPOON760
MHDTXBCBS CA1275
MHDTXAETNA1122
MHDTXMultiplan1292
MHDTXOON1777
MHRTCBCBS CA1195
MHRTCAETNA969
MHRTCMultiplan1105
MHRTCOON1456
MHPHPBCBS CA550
MHPHPAETNA195
MHPHPMultiplan833
MHPHPOON1147
MHIOPBCBS CA475
MHIOPAETNA510
MHIOPMultiplan552.5
MHIOPOON760

<!--StartFragment--> <colgroup><col width="66" span="4" style="width:66pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
 
Upvote 0
Another option is to put a small selection table off to the side with the same dropdown choices and use a sumproduct



Excel 2003
ABCDEFGHI
1FacilityLOCInsuranceAmountFacilityLOCInsuranceAmount
2LPDTXCigna750LPRTCOON1456
3LPDTXBCBS572
4LPDTXCompsych901
5LPDTXGM/Cigna600
6LPDTXValue Opt
7LPDTXOON1777
8LPRTCCigna525
9LPRTCBCBS478
10LPRTCCompsych519
11LPRTCGM/Cigna600
12LPRTCValue Opt450
13LPRTCOON1456
14LPPHPCigna395
15LPPHPBCBS264
16LPPHPCompsych641
17LPPHPGM/Cigna300
18LPPHPValue Opt340
19LPPHPOON1147
20LPIOPCigna275
21LPIOPBCBS123
22LPIOPCompsych254
23LPIOPGM/Cigna130
24LPIOPValue Opt206
25LPIOPOON760
26MHDTXBCBS CA1275
27MHDTXAETNA1122
28MHDTXMultiplan1292
29MHDTXOON1777
30MHRTCBCBS CA1195
31MHRTCAETNA969
32MHRTCMultiplan1105
33MHRTCOON1456
34MHPHPBCBS CA550
35MHPHPAETNA195
36MHPHPMultiplan833
37MHPHPOON1147
38MHIOPBCBS CA475
39MHIOPAETNA510
40MHIOPMultiplan552.5
41MHIOPOON760
Sheet1
Cell Formulas
RangeFormula
I2=SUMPRODUCT(--(A2:A41=F2)*(B2:B41=G2)*(C2:C41=H2),D2:D41)
 
Upvote 0
Michael M said:
Another option is to put a small selection table off to the side with the same dropdown choices and use a sumproduct


Excel 2003
ABCDEFGHI
1FacilityLOCInsuranceAmountFacilityLOCInsuranceAmount
2LPDTXCigna750LPRTCOON1456
3LPDTXBCBS572
4LPDTXCompsych901
5LPDTXGM/Cigna600
6LPDTXValue Opt
7LPDTXOON1777
8LPRTCCigna525
9LPRTCBCBS478
10LPRTCCompsych519
11LPRTCGM/Cigna600
12LPRTCValue Opt450
13LPRTCOON1456
14LPPHPCigna395
15LPPHPBCBS264
16LPPHPCompsych641
17LPPHPGM/Cigna300
18LPPHPValue Opt340
19LPPHPOON1147
20LPIOPCigna275
21LPIOPBCBS123
22LPIOPCompsych254
23LPIOPGM/Cigna130
24LPIOPValue Opt206
25LPIOPOON760
26MHDTXBCBS CA1275
27MHDTXAETNA1122
28MHDTXMultiplan1292
29MHDTXOON1777
30MHRTCBCBS CA1195
31MHRTCAETNA969
32MHRTCMultiplan1105
33MHRTCOON1456
34MHPHPBCBS CA550
35MHPHPAETNA195
36MHPHPMultiplan833
37MHPHPOON1147
38MHIOPBCBS CA475
39MHIOPAETNA510
40MHIOPMultiplan552.5
41MHIOPOON760

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
I2=SUMPRODUCT(--(A2:A41=F2)*(B2:B41=G2)*(C2:C41=H2),D2:D41)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Click to expand...



That would be VERY silly to do such a thing if he has the optino of using SUMIFS


please refer to Excel’s SUMIFS or SUMPRODUCT…Which is faster?

Or try it yourself as I have. I consider sumproduct an array formula (although many will argue the FACT it technically is NOT and i know this), because it performs at the speed of one. And i will always take formulas over arrays, Except in the case of non-volatile functions, although they are faster on the Full calculation of a sheet they show there true colors during recals.
 
Upvote 0
Fair comment.....but it's the best option provided so far....and it may be exactly what the OP wants...:LOL:
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VLOOKUP Help
Replies
3
Views
282
LogPlanner
L
C
  • Question
Nested if for 12 results? Can it be done?
Replies
9
Views
243
Churchy LaFemme
C
Z
  • Question
Massive Nested IF Statement Help Needed
Replies
1
Views
154
Zutalores
Z
E
  • Question
Data Filtering – Help needed with some nervous Excel users!
Replies
9
Views
763
Peter_SSs
Peter_SSs
B
  • Question
Data Table: Goal Seek Multiple Criteria
Replies
0
Views
315
bsps
B

Forum statistics

Threads
1,215,560
Messages
6,125,523
Members
449,236
Latest member
Afua

Share this page

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