IF function help, potentially a different formula woudl work easier

M

mpasce01

New Member
Joined
Jan 31, 2018
Messages
3
Hello,

I have been searching this forum and youtube all day to figure out this issue. Sorry if this is a repeat. My scenario:

I am attempting to do a crosswalk. Basically comparing a legacy system data to an upgraded new system.

I am trying to create a formula in the "Proposed S2 Activity Code" column to find the value in the "Athena Description" column and if it matches the "ALL DESCRIPTION CODES" column, then the cell in the Proposed S2 Activity Code column will equal the "ALL CODES" cell value

My formula, which I know is wrong is =IF(E2=$H$2:$H$36,$I$2:$I$36,) So as you can see from the data below it doesn't work when everything isn't lined up. The logic I am trying to create is IF cell E2 = anything in Array H2:32 then I want that cell to = the corresponding array of I2:I32. I am pretty sure I can do this pretty easily with double quotes, but I could have 700+ unique values and I don't want to type that out. Any help at all would be much appreciated and I would forever be in your debt :)


Data:

[TABLE="width: 1187"]
<tbody>[TR]
[TD]Athena Resource[/TD]
[TD]S2 Proposed Resource Code[/TD]
[TD]Characters[/TD]
[TD]Location[/TD]
[TD]Athena Description[/TD]
[TD]Proposed S2 Activity Code[/TD]
[TD][/TD]
[TD]ALL DESCRIPTION CODES[/TD]
[TD]ALL CODES[/TD]
[/TR]
[TR]
[TD]UNOR_Bullock_Trevor[/TD]
[TD]BULLOCKTRE[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Risman_DO[/TD]
[TD]Follow Up Visit[/TD]
[TD]FUV[/TD]
[TD][/TD]
[TD]Follow Up Visit[/TD]
[TD]FUV[/TD]
[/TR]
[TR]
[TD]UNOR_Bullock_Trevor[/TD]
[TD]BULLOCKTRE[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Risman_DO[/TD]
[TD]New Pt Office Visit[/TD]
[TD]NPV[/TD]
[TD][/TD]
[TD]New Pt Office Visit[/TD]
[TD]NPV[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Geauga_DO[/TD]
[TD]Est Pt Office Visit[/TD]
[TD]EPV[/TD]
[TD][/TD]
[TD]Est Pt Office Visit[/TD]
[TD]EPV[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Geauga_DO[/TD]
[TD]Est Pt-New Problem[/TD]
[TD]NEWPROB[/TD]
[TD][/TD]
[TD]Est Pt-New Problem[/TD]
[TD]NEWPROB[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Geauga_DO[/TD]
[TD]Follow Up Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Injection[/TD]
[TD]INJECTION[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Geauga_DO[/TD]
[TD]Injection[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Post Op Visit[/TD]
[TD]POV[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Geauga_DO[/TD]
[TD]New Pt Office Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Follow Up-Fracture[/TD]
[TD]FUVFRAC[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Geauga_DO[/TD]
[TD]Post Op Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Consultation[/TD]
[TD]CONSULT[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Solon 204_DO[/TD]
[TD]Est Pt Office Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Surgery[/TD]
[TD]SURGERY[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Solon 204_DO[/TD]
[TD]Est Pt-New Problem[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]ANY APPT[/TD]
[TD]DELETE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Solon 204_DO[/TD]
[TD]Follow Up Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]New Pt-Fracture[/TD]
[TD]NPVFRAC[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Solon 204_DO[/TD]
[TD]Follow Up-Fracture[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Follow Up-Test Results[/TD]
[TD]FUVRESULT[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Solon 204_DO[/TD]
[TD]Injection[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Cast Removal[/TD]
[TD]CASTREMOV[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Solon 204_DO[/TD]
[TD]New Pt Office Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]ZocDoc Est 15[/TD]
[TD]DELETE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Solon 204_DO[/TD]
[TD]Post Op Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]ZocDoc New 15[/TD]
[TD]DELETE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Streetsboro2_DO[/TD]
[TD]Est Pt-New Problem[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]BWC Exam[/TD]
[TD]NPVBWC[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Streetsboro2_DO[/TD]
[TD]Follow Up Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]BWC Follow Up[/TD]
[TD]FUVBWC[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Streetsboro2_DO[/TD]
[TD]New Pt Office Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]ZocDoc Est 20[/TD]
[TD]DELETE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Streetsboro2_DO[/TD]
[TD]Post Op Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]ZocDoc New 20[/TD]
[TD]DELETE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Surgery[/TD]
[TD]Consultation[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]ZocDoc New 30[/TD]
[TD]DELETE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Surgery[/TD]
[TD]Surgery[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Post OP-New Pt[/TD]
[TD]POVNPV[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Twinsburg_DO[/TD]
[TD]Follow Up Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]NUH Wright Surgery Center[/TD]
[TD]UNSURE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Twinsburg_DO[/TD]
[TD]Follow Up-Fracture[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]UH Ahuja Medical Center[/TD]
[TD]UNSURE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Twinsburg_DO[/TD]
[TD]Injection[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]APPROVED ONLY[/TD]
[TD]UNSURE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Twinsburg_DO[/TD]
[TD]New Pt Office Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Consult-BC[/TD]
[TD]UNSURE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD]P_UNOR_Twinsburg_DO[/TD]
[TD]Post Op Visit[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Follow Up-Hospital[/TD]
[TD]FUVHOSP[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]New Patient[/TD]
[TD]DELETE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]New Pt-Referral[/TD]
[TD]NPVRFRL[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Follow Up Visit NoEMR[/TD]
[TD]UNSURE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]New Pt Office Visit NoEMR[/TD]
[TD]UNSURE[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Neuroscan[/TD]
[TD]NEUROSCAN[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Same Day Access[/TD]
[TD]SAMEDAY[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Est Pt Physical[/TD]
[TD]EPVPHYS[/TD]
[/TR]
[TR]
[TD]UNOR_Clayton_Ali[/TD]
[TD]CLAYTONALI[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Follow Up >3 Years[/TD]
[TD]FUV3YEARS[/TD]
[/TR]
[TR]
[TD]UNOR_Corn_Robert[/TD]
[TD]CORNROBERT[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Nail Trimming[/TD]
[TD]NAILTRIM[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Welcome to the board.

It looks like you could use an INDEX/MATCH formula. Perhaps this:

=INDEX($I$2:$I$32,MATCH(E2,$H$2:$H$32,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Can anyone tell me why macro wont work in office 2015/6 but works in 2010
Replies
1
Views
874
V_Malkoti
V_Malkoti
K
  • Question Question
Need Help with DSUM function
Replies
0
Views
582
Krbass01
K
S
  • Question Question
Auto populate IE form not working
Replies
0
Views
811
Storm8585
S
L
  • Question Question
adding duplicates the sorting from largest to lowest
Replies
3
Views
2K
scottylad2
scottylad2

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

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