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:

Athena ResourceS2 Proposed Resource CodeCharactersLocationAthena DescriptionProposed S2 Activity CodeALL DESCRIPTION CODESALL CODES
UNOR_Bullock_TrevorBULLOCKTRE10P_UNOR_Risman_DOFollow Up VisitFUVFollow Up VisitFUV
UNOR_Bullock_TrevorBULLOCKTRE10P_UNOR_Risman_DONew Pt Office VisitNPVNew Pt Office VisitNPV
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Geauga_DOEst Pt Office VisitEPVEst Pt Office VisitEPV
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Geauga_DOEst Pt-New ProblemNEWPROBEst Pt-New ProblemNEWPROB
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Geauga_DOFollow Up Visit0InjectionINJECTION
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Geauga_DOInjection0Post Op VisitPOV
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Geauga_DONew Pt Office Visit0Follow Up-FractureFUVFRAC
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Geauga_DOPost Op Visit0ConsultationCONSULT
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Solon 204_DOEst Pt Office Visit0SurgerySURGERY
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Solon 204_DOEst Pt-New Problem0ANY APPTDELETE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Solon 204_DOFollow Up Visit0New Pt-FractureNPVFRAC
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Solon 204_DOFollow Up-Fracture0Follow Up-Test ResultsFUVRESULT
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Solon 204_DOInjection0Cast RemovalCASTREMOV
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Solon 204_DONew Pt Office Visit0ZocDoc Est 15DELETE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Solon 204_DOPost Op Visit0ZocDoc New 15DELETE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Streetsboro2_DOEst Pt-New Problem0BWC ExamNPVBWC
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Streetsboro2_DOFollow Up Visit0BWC Follow UpFUVBWC
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Streetsboro2_DONew Pt Office Visit0ZocDoc Est 20DELETE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Streetsboro2_DOPost Op Visit0ZocDoc New 20DELETE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_SurgeryConsultation0ZocDoc New 30DELETE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_SurgerySurgery0Post OP-New PtPOVNPV
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Twinsburg_DOFollow Up Visit0NUH Wright Surgery CenterUNSURE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Twinsburg_DOFollow Up-Fracture0UH Ahuja Medical CenterUNSURE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Twinsburg_DOInjection0APPROVED ONLYUNSURE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Twinsburg_DONew Pt Office Visit0Consult-BCUNSURE
UNOR_Clayton_AliCLAYTONALI10P_UNOR_Twinsburg_DOPost Op Visit0Follow Up-HospitalFUVHOSP
UNOR_Clayton_AliCLAYTONALI100New PatientDELETE
UNOR_Clayton_AliCLAYTONALI100New Pt-ReferralNPVRFRL
UNOR_Clayton_AliCLAYTONALI100Follow Up Visit NoEMRUNSURE
UNOR_Clayton_AliCLAYTONALI100New Pt Office Visit NoEMRUNSURE
UNOR_Clayton_AliCLAYTONALI100NeuroscanNEUROSCAN
UNOR_Clayton_AliCLAYTONALI100Same Day AccessSAMEDAY
UNOR_Clayton_AliCLAYTONALI100Est Pt PhysicalEPVPHYS
UNOR_Clayton_AliCLAYTONALI100Follow Up >3 YearsFUV3YEARS
UNOR_Corn_RobertCORNROBERT100Nail TrimmingNAILTRIM

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
Hi,

You can also use Vlookup

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

Cheers!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

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