Conditional lookup based on text and a number value.

[islic1411]

Greetings MrExcel users and Thank you for taking a minute to help me with my challenge.

I have been doing these lookups by hand and I know there has to be a faster way
Here is an example of what I am working with:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Date
[/TD]
[TD]TestType
[/TD]
[TD]ID
[/TD]
[TD]Results
[/TD]
[TD]TestDate
[/TD]
[TD]Event Key
[/TD]
[/TR]
[TR]
[TD]Person1
[/TD]
[TD]12/3/2014
[/TD]
[TD]BP_Diastolic
[/TD]
[TD]1
[/TD]
[TD]79
[/TD]
[TD]Blank Cell
[/TD]
[TD]B98D6FB8-823F-4E40-B259-0A48B34A980D
[/TD]
[/TR]
[TR]
[TD]Person2
[/TD]
[TD]01/10/2014
[/TD]
[TD]BP_Systolic
[/TD]
[TD]2
[/TD]
[TD]164
[/TD]
[TD]Blank Cell
[/TD]
[TD]6FAB3475-AF72-4301-AA5F-5977632BA3F1
[/TD]
[/TR]
[TR]
[TD]Person3
[/TD]
[TD]05/11/2014
[/TD]
[TD]HBA1C
[/TD]
[TD]3
[/TD]
[TD]6.0
[/TD]
[TD]Blank Cell
[/TD]
[TD]E27B392D-D557-411E-A5B3-1D340091244C
[/TD]
[/TR]
[TR]
[TD]Person4
[/TD]
[TD]07/11/2014
[/TD]
[TD]HBA1C
[/TD]
[TD]4
[/TD]
[TD]10.4
[/TD]
[TD]Blank Cell
[/TD]
[TD]AAD32F55-C844-432A-BC20-5A178C263026
[/TD]
[/TR]
</tbody>[/TABLE]

The issue I face: The test type along with the results score dictates what the Event Key will be.
I would like to be able to do a remote lookup (another worksheet or something) and have the formula pick out the right key.

Facts and other Data:
Sheet 1 contains all the information listed above without the key
Sheet 2 contains all the keys and a description similar to the test type but may not be a 100% text to text match
The test type list is several hundred and the list of one sheet 1 is several thousand.

What other information would be helpful?

 

[Caribeiro77]

Welcome, can you give a few examples of how's your data in sheet 2 so we can now how to lookup a match?

 

[islic1411]

Sure. Here is an example of sheet 2

[TABLE="width: 720"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Service[/TD]
[TD]EventKey[/TD]
[TD]DOS[/TD]
[TD]DOSThru[/TD]
[/TR]
[TR]
[TD]Date of Birth Correction [/TD]
[TD]02450CDF-E768-46F0-A517-B2A2AF8B66CC[/TD]
[TD]Correct DOB [/TD]
[TD]Correct DOB [/TD]
[/TR]
[TR]
[TD]Member is not diabetic[/TD]
[TD]18DD44C3-16F8-4EB8-B37B-EB9C8B7E6658[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1/1/2014[/TD]
[/TR]
[TR]
[TD]HbA1c test [/TD]
[TD]B5D8497E-A6E3-4309-A3F4-EDEFDC94F14B[/TD]
[TD]Date of Documentation [/TD]
[TD]Date of Documentation [/TD]
[/TR]
[TR]
[TD]HbA1c result <= 9.0 [/TD]
[TD]CD4496C3-87FA-4389-8EB7-E16FA1022D4D[/TD]
[TD]Date of Documentation [/TD]
[TD]Date of Documentation [/TD]
[/TR]
[TR]
[TD]HbA1c result < 8 [/TD]
[TD]10C3048F-F480-4EE3-9660-6E2ED24D9C78[/TD]
[TD]Date of Documentation [/TD]
[TD]Date of Documentation [/TD]
[/TR]
[TR]
[TD]HbA1c result < 7 [/TD]
[TD]E27B392D-D557-411E-A5B3-1D340091244C[/TD]
[TD]Date of Documentation [/TD]
[TD]Date of Documentation [/TD]
[/TR]
[TR]
[TD]HbA1c result > 9.0 [/TD]
[TD]AAD32F55-C844-432A-BC20-5A178C263026[/TD]
[TD]Date of Documentation [/TD]
[TD]Date of Documentation [/TD]
[/TR]
</tbody>[/TABLE]

 

[Caribeiro77]

Not an easy one...you have to search 2 criteria ( one as a text an another as a number) on a single cell....Hum...
Maybe someone arround here will give you a solution, since i'm not able to give you one...