IF function help, potentially a different formula woudl work easier

[mpasce01]

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]

 

[dreid1011]

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

 

[arunsjain]

Hi,

You can also use Vlookup

=VLOOKUP(E2,H:I,2,FALSE)

Cheers!!

 

[mpasce01]

BOOM! Thank you so much! HUGE help! If I knew who you were I would buy you a beer.

 

[mpasce01]

Also the formula that worked was this =INDEX($I$2:$I$32,MATCH(E2,$H$2:$H$32,0)). THanks!

 

[dreid1011]

You're welcome.