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!
 
Sort by date Sort by votes
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...
Hi Peter, is there a way to do this with Index Match? I now have access to complete data and there the activity codes are repetitive to difference PC codes. Index Match will be a good fit for for this. Thanks in advance.
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
mohdabrar said:
is there a way to do this with Index Match?
Click to expand...
Sure. For that same layout ..
Q2:
Excel Formula: 
=IFNA(INDEX(B$2:B$50,MATCH(M2,A$2:A$50,0)),IFNA(INDEX(H$2:H$50,MATCH(N2,G$2:G$50,0)),""))

R2:
Excel Formula: 
=IFNA(INDEX(E$2:E$50,MATCH(M2,D$2:D$50,0)),IFNA(INDEX(K$2:K$50,MATCH(N2,J$2:J$50,0)),""))
 
Upvote 0
Solution
Thanks Peter, this works. However, I am working for people who don't know what they're looking for. ;) I need one last modification for this. I'll share the dummy file in some time. Is there a possibility to attach an excel, because I specifically have to create a dummy file which takes a lot of time and I keep missing the information that actually needs to be matched. Let me know or I'll just create a dummy sheet and explain what I need. Thanks!
 
Upvote 0
mohdabrar said:
Is there a possibility to attach an excel,
Click to expand...
No, the forum does not permit uploading of actual files. XL2BB is generally the best way. It is possible to upload a file elsewhere and provide a public shared link in the forum but many helpers here are unwilling or unable due to workplace security to access such files. Further, in my experience such files often confuse the issue and are much harder for helpers to understand.

mohdabrar said:
because I specifically have to create a dummy file which takes a lot of time and I keep missing the information that actually needs to be matched.
Click to expand...
If you are looking for help, my logic says that you should be prepared to spend that time making a small but representative and accurate dummy file that will be easy for helpers to understand, follow and use in their testing. :)
 
Upvote 0
Thanks for the response. Please find bellow the data. In column P I might have CPT or Activity Codes separated by a "," Z36.9,Z83.3,Z3A.25,Z23, This is a clubbed column for all the required CPT or Activity codes. Is there a way to look up these values from one column, and get the result in Numerator/Denominator? I would still keep the initial formula, and add this in the mix. Hope this is clear explanation. Thanks again!

Dummy Data PC Codes.xlsx
ABCDEFGHIJKLMNOPQR
1CPTKPI - NumeratorCPTKPI - DenominatorCPTNumeratorICDDenominatorActivity CodeDiagnosisType1DiagnosisType2DiagnosisType3KPI NumeratorKPI Denominator
258150Q100411000QI005-Denominator77065PC001-NumeratorZ12.31PC001-Denominator58150O47.1Z3A.37Z36.9, Z83.3, Z3A.25, Z23Q1004
358152Q100411004QI005-Denominator77066PC001-NumeratorZ12.39PC001-Denominator58152U07.1Z36.9, Z83.3, Z3A.25, Z23Q1004
458180Q100411005QI005-Denominator77067PC001-NumeratorZ11.51PC002-Denominator58180M48.55XAZ91.81Z36.9, Z83.3, Z3A.25, Z23Q1004
558200Q100411006QI005-Denominator87620PC002-NumeratorZ12.4PC002-Denominator58200M48.55XAW19.XXXAZ36.9, Z83.3, Z3A.25, Z23Q1004
658210Q100411008QI005-Denominator87621PC002-NumeratorZ12.11PC003-Denominator58210U07.1R05R50.83Q1004
758541Q100411010QI005-Denominator87622PC002-NumeratorZ12.12PC003-Denominator11000Z12.31R10.9B96.89, R52QI005-Denominator
858542Q100411011QI005-Denominator88141PC002-NumeratorF32.0PC005-Denominator11004Z12.39B96.89, R52QI005-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.XXXSR50.83QI005-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.18R50.83
1658573Q100415150QI005-Denominator88154PC002-NumeratorF33.0PC005-DenominatorI80.201F32.8Z13.228
1758951Q100415151QI005-Denominator88164PC002-NumeratorF33.1PC005-DenominatorI80.202Z38.01Z13.5
1858953Q100415155QI005-Denominator88165PC002-NumeratorF33.2PC005-DenominatorA4649Z38.01Z13.5R50.83
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.1R42R11.10, R53.83
42I80.291QI005-Numerator15750QI005-Denominator13217PC-006-NumeratorO03.30PC-006-DenominatorE0325U07.1J20.8R11.10, R53.83
43I80.292QI005-Numerator15756QI005-Denominator13219PC-006-NumeratorO03.31PC-006-DenominatorE0325U07.1Z99.89X58.XXXA, E03.9, E53.8, R23.1
44I80.293QI005-Numerator15757QI005-Denominator13221PC-006-NumeratorO03.32PC-006-DenominatorE0325T67.3XXAI10X58.XXXA, E03.9, E53.8, R23.1
45I80.299QI005-Numerator15758QI005-Denominator13222PC-006-NumeratorO03.33PC-006-DenominatorA4649U07.1J45.909X58.XXXA, E03.9, E53.8, R23.1
46I80.8QI005-Numerator15760QI005-Denominator13223PC-006-NumeratorO03.34PC-006-DenominatorA4649U07.1J12.82X58.XXXA, E03.9, E53.8, R23.1
47I80.9QI005-Numerator15770QI005-Denominator13224PC-006-NumeratorO03.35PC-006-DenominatorA4649I21.4B96.20X58.XXXA, E03.9, E53.8, R23.1
48I82.401QI005-Numerator15830QI005-Denominator13227PC-006-NumeratorO03.36PC-006-DenominatorA4649R56.1Z87.01X58.XXXA, E03.9, E53.8, R23.1
49I82.402QI005-Numerator15840QI005-Denominator13229PC-006-NumeratorO03.37PC-006-DenominatorA4649U07.1X58.XXXA, E03.9, E53.8, R23.1
50I82.403QI005-Numerator15841QI005-Denominator13241PC-006-NumeratorO03.38PC-006-DenominatorA4649K29.70B96.81X58.XXXA, E03.9, E53.8, R23.1
For XSLB
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D50Cell ValueduplicatestextNO
 
Upvote 0
mohdabrar said:
Hope this is clear explanation.
Click to expand...
No, it isn't clear to me. Are they the expected results in columns Q & R?
- If so, the existing formula produces them.
- If not, what are the expected results?

I don't understand what, if anything, extra needs to be done.
 
Upvote 0
Hi Peter,

So, I created the below Raw Data, I hope this will give an idea what I am expected to do. I've removed a few columns because the data was repeating. In the below data, just like the previous solution, I am trying to look for values that match from column M & N and get the result in column O & P. However, the only difference here from the earlier data is in Column N, I have the data as Z36.9,Z83.3,Z3A.25,Z23 - and I need to match the codes separated by "," as shown in Column P. I hope this provides clarity. Thank you for your help.

Dummy Data PC Codes.xlsx
ABCDEFGHIJKLMNOP
1CPTKPI - NumeratorCPTKPI - DenominatorCPTNumeratorICDDenominatorActivity CodeDiagnosisType3KPI NumeratorKPI Denominator
258150Q1004Z36.9QI005-Denominator77065PC001-NumeratorZ12.31PC001-Denominator58150Z36.9, Z83.3, Z3A.25, Z23Q1004QI005-Denominator (Z36.9)
358152Q1004Z83.3QI005-Denominator77066PC001-NumeratorZ12.39PC001-Denominator58152Z36.0, Z83.3, Z3A.25, Z23Q1004QI005-Denominator (Z83.3)
458180Q1004Z3A.25QI005-Denominator77067PC001-NumeratorZ11.51PC002-Denominator58180Z36.9, Z83.3, Z3A.25, Z23Q1004QI005-Denominator (Z3A.25)
558200Q1004R50.83QI005-Denominator87620PC002-NumeratorZ12.4PC002-Denominator58200Z36.9, Z83.3, Z3A.25, Z23Q1004
658210Q100411008QI005-Denominator87621PC002-NumeratorZ12.11PC003-Denominator58210R50.83Q1004
758541Q100411010QI005-Denominator87622PC002-NumeratorZ12.12PC003-Denominator11000B96.89, R52
858542Q100411011QI005-Denominator88141PC002-NumeratorF32.0PC005-Denominator11004B96.89, R52
958543Q100411012QI005-Denominator88142PC002-NumeratorF32.1PC005-Denominator11005
1058544Q100411042QI005-Denominator88143PC002-NumeratorF32.2PC005-Denominator11006
1158548Q100411043QI005-Denominator88147PC002-NumeratorF32.3PC005-Denominator11008
1258548Q100411044QI005-Denominator88148PC002-NumeratorF32.4PC005-DenominatorI80.10R50.83QI005-Numerator
1358570Q100411960QI005-Denominator88150PC002-NumeratorF32.5PC005-DenominatorI80.11
1458571Q100414301QI005-Denominator88152PC002-NumeratorF32.8PC005-DenominatorI80.12
1558572Q100414302QI005-Denominator88153PC002-NumeratorF32.9PC005-DenominatorI80.13R50.83
1658573Q100415150QI005-Denominator88154PC002-NumeratorF33.0PC005-DenominatorI80.201
1758951Q100415151QI005-Denominator88164PC002-NumeratorF33.1PC005-DenominatorI80.202
1858953Q100415155QI005-Denominator88165PC002-NumeratorF33.2PC005-DenominatorA4649R50.83
1958954Q100415156QI005-Denominator88166PC002-NumeratorF33.3PC005-DenominatorA4649
2058956Q100415200QI005-Denominator88167PC002-NumeratorF33.40PC005-DenominatorA4649
21I80.10QI005-Numerator15201QI005-Denominator88174PC002-NumeratorF33.41PC005-DenominatorA4649
22I80.11QI005-Numerator15220QI005-Denominator88175PC002-NumeratorF33.42PC005-DenominatorA4649
23I80.12QI005-Numerator15221QI005-Denominator82274PC003-NumeratorF33.8PC005-DenominatorA4649
24I80.13QI005-Numerator15240QI005-Denominator3351FPC004-NumeratorF33.9PC005-DenominatorA4649
25I80.201QI005-Numerator15260QI005-Denominator3353FPC004-NumeratorZ13.89PC005-DenominatorA4649
26I80.202QI005-Numerator15261QI005-Denominator3354FPC004-NumeratorO00.0PC-006-DenominatorA4649
27I80.203QI005-Numerator15570QI005-DenominatorF01 to F98.9PC004-NumeratorO00.1PC-006-DenominatorA4649
28I80.209QI005-Numerator15572QI005-Denominator99211PC005-NumeratorO00.2PC-006-DenominatorA4649
29I80.3QI005-Numerator15574QI005-Denominator99212PC005-NumeratorO00.8PC-006-DenominatorA4649
30I80.211QI005-Numerator15576QI005-Denominator99213PC005-NumeratorO00.9PC-006-DenominatorA4649
31I80.212QI005-Numerator15600QI005-Denominator99214PC005-NumeratorO01.0PC-006-DenominatorA4649
32I80.213QI005-Numerator15610QI005-Denominator99215PC005-NumeratorO01.1PC-006-DenominatorA4649
33I80.219QI005-Numerator15620QI005-Denominator99241PC005-NumeratorO01.9PC-006-DenominatorA4649
34I80.221QI005-Numerator15630QI005-Denominator99242PC005-NumeratorO02.0PC-006-DenominatorA4649
35I80.222QI005-Numerator15650QI005-Denominator99243PC005-NumeratorO02.1PC-006-DenominatorA4649
36I80.223QI005-Numerator15731QI005-Denominator99244PC005-NumeratorO02.81PC-006-DenominatorA4649
37I80.229QI005-Numerator15732QI005-Denominator99245PC005-NumeratorO02.89PC-006-DenominatorA4649
38I80.231QI005-Numerator15734QI005-Denominator13211PC-006-NumeratorO02.9PC-006-DenominatorA4649
39I80.232QI005-Numerator15736QI005-Denominator13212PC-006-NumeratorO03.0PC-006-DenominatorA4649
40I80.233QI005-Numerator15738QI005-Denominator13213PC-006-NumeratorO03.1PC-006-DenominatorA4649
41I80.239QI005-Numerator15740QI005-Denominator13214PC-006-NumeratorO03.2PC-006-DenominatorE0325R11.10, R53.83
42I80.291QI005-Numerator15750QI005-Denominator13217PC-006-NumeratorO03.30PC-006-DenominatorE0325R11.10, R53.83
43I80.292QI005-Numerator15756QI005-Denominator13219PC-006-NumeratorO03.31PC-006-DenominatorE0325X58.XXXA, E03.9, E53.8, R23.1
44I80.293QI005-Numerator15757QI005-Denominator13221PC-006-NumeratorO03.32PC-006-DenominatorE0325X58.XXXA, E03.9, E53.8, R23.1
45I80.299QI005-Numerator15758QI005-Denominator13222PC-006-NumeratorO03.33PC-006-DenominatorA4649X58.XXXA, E03.9, E53.8, R23.1
46I80.8QI005-Numerator15760QI005-Denominator13223PC-006-NumeratorO03.34PC-006-DenominatorA4649X58.XXXA, E03.9, E53.8, R23.1
47I80.9QI005-Numerator15770QI005-Denominator13224PC-006-NumeratorO03.35PC-006-DenominatorA4649X58.XXXA, E03.9, E53.8, R23.1
48I82.401QI005-Numerator15830QI005-Denominator13227PC-006-NumeratorO03.36PC-006-DenominatorA4649X58.XXXA, E03.9, E53.8, R23.1
49I82.402QI005-Numerator15840QI005-Denominator13229PC-006-NumeratorO03.37PC-006-DenominatorA4649X58.XXXA, E03.9, E53.8, R23.1
50I82.403QI005-Numerator15841QI005-Denominator13241PC-006-NumeratorO03.38PC-006-DenominatorA4649X58.XXXA, E03.9, E53.8, R23.1
For XSLB
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D50Cell ValueduplicatestextNO
 
Upvote 0
I'm afraid that I am not seeing a way to produce those results.
 
Upvote 0
It may even be possible with formulas but I'm sure it would be possible with vba but it is not a task I would take on in a free public forum like this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Help with LOOKUP
Replies
1
Views
194
Joe4
Joe4
S
  • Question Question
Multiple columns lookup
Replies
0
Views
262
Sipirili2504
S
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
286
etaf
etaf
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
162
Peter_SSs
Peter_SSs
J
  • Solved
VBA value to left of column
Replies
5
Views
46
MARK858
MARK858

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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