VLOOKUP or INDEX MATCH HELP (Multiple Columns Lookup)

mohdabrar

New Member
Joined
Aug 4, 2015
Messages
37
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows
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.
 

mohdabrar

New Member
Joined
Aug 4, 2015
Messages
37
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows
I have a lot of data so I am just including a little of it to explain the problem.
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?
 

mohdabrar

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

ADVERTISEMENT

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?
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows
please let me know if you need more info.
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.
 

mohdabrar

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

ADVERTISEMENT

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.
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows

mohdabrar

New Member
Joined
Aug 4, 2015
Messages
37
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hmm, see if this is it

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),""))
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,569
Messages
5,770,907
Members
425,651
Latest member
Mark Cashin

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
Top