VLOOKUP or INDEX MATCH HELP (Multiple Columns Lookup)

M

mohdabrar

New Member
Joined
Aug 4, 2015
Messages
40
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

I have two sheets, Sheet 1 and Sheet 2. Sheet 1 has reference values and sheet 2 has data. I have data in columns A,B,C,D in Sheet 2, and I want to lookup and match data from Sheet 1. Here's where it gets tricky- I want the formula to find a match in Column B first and if it can't find match/lookup value, it needs to move to column c and d. In the reference sheet there are just two columns, A & B, looking the value in A and Result value in B. I've tried to combine If and Match and Vlookup but to no avail. Any help is greatly appreciated.

Thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
I think it might help clarify if we had small sets of sample data and expected results from both worksheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
I am merging the sheet in one, I forgot one important detail, and I apologize for that. Below is the data set - Columns A-F are sheet 1 and J-M are sheet 2. I have a lot of data so I am just including a little of it to explain the problem. I am trying to look the activity code in CPT and Numerator on sheet 1, however, if I am unable to find a result, I need to lookup the same in Diagnosis 1 and so on. Also, the one detail I forgot was that my sheet 1 doesn't just have A and B but also E& F columns. I need to lookup the Numerator values in column L and Denominator Values in Column M in the same way, starting from Activity, Diagnosis 1 and so on. Please let me know if you have any questions.

Book1
ABCDEFGHIJKLM
1CPTNumeratorICDDenominatorActivityCodeDiagnosisType1DiagnosisType2DiagnosisType3Kpi NumeratorKPI Denominator
277065PC001-NumeratorZ12.31PC001-Denominator99201U07.1J06.9Need Numerator Value hereNeed Denoinator Value here
377066PC001-NumeratorZ12.39PC001-Denominator99214U07.1
477067PC001-NumeratorZ12.4PC002-Denominator99201U07.1J30.9
588141PC002-NumeratorZ11.51PC002-Denominator99214U07.1
688142PC002-NumeratorZ12.11PC003-Denominator99201U07.1R05
788143PC002-NumeratorZ12.12PC003-Denominator99201U07.1J20.9
888147PC002-NumeratorZ13.89PC005-Denominator99201U07.1R50.81
988148PC002-NumeratorF32.0PC005-Denominator99201U07.1R51
1088150PC002-NumeratorF32.1PC005-Denominator99201U07.1R05
1188152PC002-NumeratorF32.2PC005-Denominator99201U07.1R50.81
1288153PC002-NumeratorF32.3PC005-Denominator99201U07.1R05
1388165PC002-NumeratorF32.4PC005-Denominator99201U07.1M79.1
1488166PC002-NumeratorF32.5PC005-Denominator99201U07.1I95.9
1588167PC002-NumeratorF32.8PC005-Denominator99201J20.9R07.0
1688174PC002-NumeratorF32.9PC005-Denominator99201U07.1
1788175PC002-NumeratorF33.0PC005-Denominator99201U07.1R07.0
1888154PC002-NumeratorF33.1PC005-Denominator99201P59.9
1988164PC002-NumeratorF33.2PC005-Denominator99202U07.1
Sheet1
 
Upvote 0
mohdabrar said:
I have a lot of data so I am just including a little of it to explain the problem.
Click to expand...
I can't see the link between the values in columns A:D and what you have on the right. I suspect that is because of the quote above.
Can you make up a small set of sample data where there are links and include the (manually entered) expected results on the right and explain again in relation to that new sample data?
If there are links in the data already provided and I have just missed them can you still include the expected results and post the mini-sheet again?
 
Upvote 0
Peter_SSs said:
I can't see the link between the values in columns A:D and what you have on the right. I suspect that is because of the quote above.
Can you make up a small set of sample data where there are links and include the (manually entered) expected results on the right and explain again in relation to that new sample data?
If there are links in the data already provided and I have just missed them can you still include the expected results and post the mini-sheet again?
Click to expand...
Hi Peter,

Including the data again below. The data has changed a bit since last time, I was asked to include another string of columns this morning. I apologize. So here's what I am trying to do. I will try to explain it as simply as possible, please let me know if you need more info. Column's A to E are KPI numerators and Denominator. Column G to K are CPT and ICD numerator and Denominator. I am trying to look data from Column M from column A to E and in Column Q and R for Numerator and Denominator respectively... If Activity Code (Column M) matched in Numerator KPI, that is Column A and B, then the formula should look the diagnosis Type 1 Column N in columns G to H. and the same goes for Denominator, except if the activity code matched in column D to E, then Diagnosis 1 should be looked up in column J to K. I hope this is explained clearly. Please let me know if you have any questions.

Dummy Data PC Codes.xlsx
ABCDEFGHIJKLMNOPQR
1CPTKPI - NumeratorCPTKPI - DenominatorCPTNumeratorICDDenominatorActivity CodeDiagnosisType1DiagnosisType2DiagnosisType3KPI NumeratorKPI Denominator
258150Q100411000QI005-Denominator77065PC001-NumeratorZ12.31PC001-Denominator58150O47.1Z3A.37Q1004
358152Q100411004QI005-Denominator77066PC001-NumeratorZ12.39PC001-Denominator58152U07.1
458180Q100411005QI005-Denominator77067PC001-NumeratorZ11.51PC002-Denominator58180M48.55XAZ91.81
558200Q100411006QI005-Denominator87620PC002-NumeratorZ12.4PC002-Denominator58200M48.55XAW19.XXXA
658210Q100411008QI005-Denominator87621PC002-NumeratorZ12.11PC003-Denominator58210U07.1R05
758541Q100411010QI005-Denominator87622PC002-NumeratorZ12.12PC003-Denominator11000Z12.31R10.9QI005-Denominator
858542Q100411011QI005-Denominator88141PC002-NumeratorF32.0PC005-Denominator11004Z12.39
958543Q100411012QI005-Denominator88142PC002-NumeratorF32.1PC005-Denominator11005Z11.51Z23
1058544Q100411042QI005-Denominator88143PC002-NumeratorF32.2PC005-Denominator11006Z12.4X30.XXXA
1158548Q100411043QI005-Denominator88147PC002-NumeratorF32.3PC005-Denominator11008Z12.11J12.82
1258548Q100411044QI005-Denominator88148PC002-NumeratorF32.4PC005-DenominatorI80.10F32.2X58.XXXSQI005-NumeratorPC005-Denominator
1358570Q100411960QI005-Denominator88150PC002-NumeratorF32.5PC005-DenominatorI80.11F32.3J96.01
1458571Q100414301QI005-Denominator88152PC002-NumeratorF32.8PC005-DenominatorI80.12F32.4Z13.5
1558572Q100414302QI005-Denominator88153PC002-NumeratorF32.9PC005-DenominatorI80.13F32.5P07.18
1658573Q100415150QI005-Denominator88154PC002-NumeratorF33.0PC005-DenominatorI80.201F32.8Z13.228
1758951Q100415151QI005-Denominator88164PC002-NumeratorF33.1PC005-DenominatorI80.202Z38.01Z13.5
1858953Q100415155QI005-Denominator88165PC002-NumeratorF33.2PC005-DenominatorA4649Z38.01Z13.5
1958954Q100415156QI005-Denominator88166PC002-NumeratorF33.3PC005-DenominatorA4649Z38.01Z13.228
2058956Q100415200QI005-Denominator88167PC002-NumeratorF33.40PC005-DenominatorA4649Z38.00Z13.5
21I80.10QI005-Numerator15201QI005-Denominator88174PC002-NumeratorF33.41PC005-DenominatorA4649Z38.00Z23
22I80.11QI005-Numerator15220QI005-Denominator88175PC002-NumeratorF33.42PC005-DenominatorA4649Z38.00Z13.228
23I80.12QI005-Numerator15221QI005-Denominator82274PC003-NumeratorF33.8PC005-DenominatorA4649Z38.01Z13.6
24I80.13QI005-Numerator15240QI005-Denominator3351FPC004-NumeratorF33.9PC005-DenominatorA4649U07.1D72.819
25I80.201QI005-Numerator15260QI005-Denominator3353FPC004-NumeratorZ13.89PC005-DenominatorA4649U07.1J12.82
26I80.202QI005-Numerator15261QI005-Denominator3354FPC004-NumeratorO00.0PC-006-DenominatorA4649P59.9
27I80.203QI005-Numerator15570QI005-DenominatorF01 to F98.9PC004-NumeratorO00.1PC-006-DenominatorA4649Z38.00Z13.6
28I80.209QI005-Numerator15572QI005-Denominator99211PC005-NumeratorO00.2PC-006-DenominatorA4649Z38.00Z13.6
29I80.3QI005-Numerator15574QI005-Denominator99212PC005-NumeratorO00.8PC-006-DenominatorA4649Z38.00Z13.6
30I80.211QI005-Numerator15576QI005-Denominator99213PC005-NumeratorO00.9PC-006-DenominatorA4649Z38.00Z13.5
31I80.212QI005-Numerator15600QI005-Denominator99214PC005-NumeratorO01.0PC-006-DenominatorA4649U07.1R09.02
32I80.213QI005-Numerator15610QI005-Denominator99215PC005-NumeratorO01.1PC-006-DenominatorA4649Z38.01Z13.6
33I80.219QI005-Numerator15620QI005-Denominator99241PC005-NumeratorO01.9PC-006-DenominatorA4649U07.1J12.82
34I80.221QI005-Numerator15630QI005-Denominator99242PC005-NumeratorO02.0PC-006-DenominatorA4649Z38.00Z13.228
35I80.222QI005-Numerator15650QI005-Denominator99243PC005-NumeratorO02.1PC-006-DenominatorA4649U07.1R19.7
36I80.223QI005-Numerator15731QI005-Denominator99244PC005-NumeratorO02.81PC-006-DenominatorA4649Z38.00Z23
37I80.229QI005-Numerator15732QI005-Denominator99245PC005-NumeratorO02.89PC-006-DenominatorA4649Z38.00Z13.5
38I80.231QI005-Numerator15734QI005-Denominator13211PC-006-NumeratorO02.9PC-006-DenominatorA4649Z38.00Z13.5
39I80.232QI005-Numerator15736QI005-Denominator13212PC-006-NumeratorO03.0PC-006-DenominatorA4649U07.1J96.01
40I80.233QI005-Numerator15738QI005-Denominator13213PC-006-NumeratorO03.1PC-006-DenominatorA4649U07.1J12.82
41I80.239QI005-Numerator15740QI005-Denominator13214PC-006-NumeratorO03.2PC-006-DenominatorE0325U07.1R42
42I80.291QI005-Numerator15750QI005-Denominator13217PC-006-NumeratorO03.30PC-006-DenominatorE0325U07.1J20.8
43I80.292QI005-Numerator15756QI005-Denominator13219PC-006-NumeratorO03.31PC-006-DenominatorE0325U07.1Z99.89
44I80.293QI005-Numerator15757QI005-Denominator13221PC-006-NumeratorO03.32PC-006-DenominatorE0325T67.3XXAI10
45I80.299QI005-Numerator15758QI005-Denominator13222PC-006-NumeratorO03.33PC-006-DenominatorA4649U07.1J45.909
46I80.8QI005-Numerator15760QI005-Denominator13223PC-006-NumeratorO03.34PC-006-DenominatorA4649U07.1J12.82
47I80.9QI005-Numerator15770QI005-Denominator13224PC-006-NumeratorO03.35PC-006-DenominatorA4649I21.4B96.20
48I82.401QI005-Numerator15830QI005-Denominator13227PC-006-NumeratorO03.36PC-006-DenominatorA4649R56.1Z87.01
49I82.402QI005-Numerator15840QI005-Denominator13229PC-006-NumeratorO03.37PC-006-DenominatorA4649U07.1
50I82.403QI005-Numerator15841QI005-Denominator13241PC-006-NumeratorO03.38PC-006-DenominatorA4649K29.70B96.81
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D50Cell ValueduplicatestextNO
 
Upvote 0
mohdabrar said:
please let me know if you need more info.
Click to expand...
It is still not clear to me.
Have you included all the expected results?
If not, please post again with all the expected results at least in rows 2 to 12.
 
Upvote 0
Peter_SSs said:
It is still not clear to me.
Have you included all the expected results?
If not, please post again with all the expected results at least in rows 2 to 12.
Click to expand...
I've included the expected results in column Q & R. I'll try to explain with the 12th row as an example. The Activity is found in Numerator, and Diagnosis is in Denominator. The Formula in Numerator, Column Q Should first look for a match of both, activity and Diagnosis in columns A & B and G & H. as those are the numerator KPI's and CPT and then, for Denominator, the formula should be in column R looking for match of Activity and Diagnosis 1 in D & E and J & K. Hope this helps.

Dummy Data PC Codes.xlsx
ABCDEFGHIJKLMNOPQR
1CPTKPI - NumeratorCPTKPI - DenominatorCPTNumeratorICDDenominatorActivity CodeDiagnosisType1DiagnosisType2DiagnosisType3KPI NumeratorKPI Denominator
258150Q100411000QI005-Denominator77065PC001-NumeratorZ12.31PC001-Denominator58150O47.1Z3A.37Q1004
358152Q100411004QI005-Denominator77066PC001-NumeratorZ12.39PC001-Denominator58152U07.1Q1004
458180Q100411005QI005-Denominator77067PC001-NumeratorZ11.51PC002-Denominator58180M48.55XAZ91.81Q1004
558200Q100411006QI005-Denominator87620PC002-NumeratorZ12.4PC002-Denominator58200M48.55XAW19.XXXAQ1004
658210Q100411008QI005-Denominator87621PC002-NumeratorZ12.11PC003-Denominator58210U07.1R05Q1004
758541Q100411010QI005-Denominator87622PC002-NumeratorZ12.12PC003-Denominator11000Z12.31R10.9QI005-Denominator
858542Q100411011QI005-Denominator88141PC002-NumeratorF32.0PC005-Denominator11004Z12.39QI005-Denominator
958543Q100411012QI005-Denominator88142PC002-NumeratorF32.1PC005-Denominator11005Z11.51Z23QI005-Denominator
1058544Q100411042QI005-Denominator88143PC002-NumeratorF32.2PC005-Denominator11006Z12.4X30.XXXAQI005-Denominator
1158548Q100411043QI005-Denominator88147PC002-NumeratorF32.3PC005-Denominator11008Z12.11J12.82QI005-Denominator
1258548Q100411044QI005-Denominator88148PC002-NumeratorF32.4PC005-DenominatorI80.10F32.2X58.XXXSQI005-NumeratorPC005-Denominator
1358570Q100411960QI005-Denominator88150PC002-NumeratorF32.5PC005-DenominatorI80.11F32.3J96.01
1458571Q100414301QI005-Denominator88152PC002-NumeratorF32.8PC005-DenominatorI80.12F32.4Z13.5
1558572Q100414302QI005-Denominator88153PC002-NumeratorF32.9PC005-DenominatorI80.13F32.5P07.18
1658573Q100415150QI005-Denominator88154PC002-NumeratorF33.0PC005-DenominatorI80.201F32.8Z13.228
1758951Q100415151QI005-Denominator88164PC002-NumeratorF33.1PC005-DenominatorI80.202Z38.01Z13.5
1858953Q100415155QI005-Denominator88165PC002-NumeratorF33.2PC005-DenominatorA4649Z38.01Z13.5
1958954Q100415156QI005-Denominator88166PC002-NumeratorF33.3PC005-DenominatorA4649Z38.01Z13.228
2058956Q100415200QI005-Denominator88167PC002-NumeratorF33.40PC005-DenominatorA4649Z38.00Z13.5
21I80.10QI005-Numerator15201QI005-Denominator88174PC002-NumeratorF33.41PC005-DenominatorA4649Z38.00Z23
22I80.11QI005-Numerator15220QI005-Denominator88175PC002-NumeratorF33.42PC005-DenominatorA4649Z38.00Z13.228
23I80.12QI005-Numerator15221QI005-Denominator82274PC003-NumeratorF33.8PC005-DenominatorA4649Z38.01Z13.6
24I80.13QI005-Numerator15240QI005-Denominator3351FPC004-NumeratorF33.9PC005-DenominatorA4649U07.1D72.819
25I80.201QI005-Numerator15260QI005-Denominator3353FPC004-NumeratorZ13.89PC005-DenominatorA4649U07.1J12.82
26I80.202QI005-Numerator15261QI005-Denominator3354FPC004-NumeratorO00.0PC-006-DenominatorA4649P59.9
27I80.203QI005-Numerator15570QI005-DenominatorF01 to F98.9PC004-NumeratorO00.1PC-006-DenominatorA4649Z38.00Z13.6
28I80.209QI005-Numerator15572QI005-Denominator99211PC005-NumeratorO00.2PC-006-DenominatorA4649Z38.00Z13.6
29I80.3QI005-Numerator15574QI005-Denominator99212PC005-NumeratorO00.8PC-006-DenominatorA4649Z38.00Z13.6
30I80.211QI005-Numerator15576QI005-Denominator99213PC005-NumeratorO00.9PC-006-DenominatorA4649Z38.00Z13.5
31I80.212QI005-Numerator15600QI005-Denominator99214PC005-NumeratorO01.0PC-006-DenominatorA4649U07.1R09.02
32I80.213QI005-Numerator15610QI005-Denominator99215PC005-NumeratorO01.1PC-006-DenominatorA4649Z38.01Z13.6
33I80.219QI005-Numerator15620QI005-Denominator99241PC005-NumeratorO01.9PC-006-DenominatorA4649U07.1J12.82
34I80.221QI005-Numerator15630QI005-Denominator99242PC005-NumeratorO02.0PC-006-DenominatorA4649Z38.00Z13.228
35I80.222QI005-Numerator15650QI005-Denominator99243PC005-NumeratorO02.1PC-006-DenominatorA4649U07.1R19.7
36I80.223QI005-Numerator15731QI005-Denominator99244PC005-NumeratorO02.81PC-006-DenominatorA4649Z38.00Z23
37I80.229QI005-Numerator15732QI005-Denominator99245PC005-NumeratorO02.89PC-006-DenominatorA4649Z38.00Z13.5
38I80.231QI005-Numerator15734QI005-Denominator13211PC-006-NumeratorO02.9PC-006-DenominatorA4649Z38.00Z13.5
39I80.232QI005-Numerator15736QI005-Denominator13212PC-006-NumeratorO03.0PC-006-DenominatorA4649U07.1J96.01
40I80.233QI005-Numerator15738QI005-Denominator13213PC-006-NumeratorO03.1PC-006-DenominatorA4649U07.1J12.82
41I80.239QI005-Numerator15740QI005-Denominator13214PC-006-NumeratorO03.2PC-006-DenominatorE0325U07.1R42
42I80.291QI005-Numerator15750QI005-Denominator13217PC-006-NumeratorO03.30PC-006-DenominatorE0325U07.1J20.8
43I80.292QI005-Numerator15756QI005-Denominator13219PC-006-NumeratorO03.31PC-006-DenominatorE0325U07.1Z99.89
44I80.293QI005-Numerator15757QI005-Denominator13221PC-006-NumeratorO03.32PC-006-DenominatorE0325T67.3XXAI10
45I80.299QI005-Numerator15758QI005-Denominator13222PC-006-NumeratorO03.33PC-006-DenominatorA4649U07.1J45.909
46I80.8QI005-Numerator15760QI005-Denominator13223PC-006-NumeratorO03.34PC-006-DenominatorA4649U07.1J12.82
47I80.9QI005-Numerator15770QI005-Denominator13224PC-006-NumeratorO03.35PC-006-DenominatorA4649I21.4B96.20
48I82.401QI005-Numerator15830QI005-Denominator13227PC-006-NumeratorO03.36PC-006-DenominatorA4649R56.1Z87.01
49I82.402QI005-Numerator15840QI005-Denominator13229PC-006-NumeratorO03.37PC-006-DenominatorA4649U07.1
50I82.403QI005-Numerator15841QI005-Denominator13241PC-006-NumeratorO03.38PC-006-DenominatorA4649K29.70B96.81
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D50Cell ValueduplicatestextNO
 
Upvote 0
Upvote 0
Peter_SSs said:
Hmm, see if this is it

mohdabrar.xlsm
QR
1KPI NumeratorKPI Denominator
2Q1004 
3Q1004 
4Q1004 
5Q1004 
6Q1004 
7 QI005-Denominator
8 QI005-Denominator
9 QI005-Denominator
10 QI005-Denominator
11 QI005-Denominator
12QI005-NumeratorPC005-Denominator
Sheet2
Cell Formulas
RangeFormula
Q2:Q12Q2=IFNA(VLOOKUP(M2,A$2:B$50,2,0),IFNA(VLOOKUP(N2,G$2:H$50,2,0),""))
R2:R12R2=IFNA(VLOOKUP(M2,D$2:E$50,2,0),IFNA(VLOOKUP(N2,J$2:K$50,2,0),""))
Click to expand...
Thank you! I am running this in the report now and will mark as solved as soon as I get the result I am looking for, or will be here with more questions. :)

Thanks again Peter!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Help with LOOKUP
Replies
1
Views
247
Joe4
Joe4
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
298
henrik2h
H
S
  • Question Question
Multiple columns lookup
Replies
0
Views
286
Sipirili2504
S
M
  • Question Question
Using text input box to call cell values.
2
Replies
12
Views
219
Skyybot
Skyybot
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
386
etaf
etaf

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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