Search for a value in a big big table based on 2 criteras : simple problem excel

[robin11]

Hello everyone. I have a little problem. I have a huge table. At the top I have the different age groups of my patients and on the left I have the raw scores that my patients can have. By combining their age and their raw score (corresponds to the cells in yellow), I obtain a standardized score (which corresponds to my cellsin orange). I enter in my cells B42 and B43 respectively the age group and the raw score of my patient and I would like in my box B43 to put a formula that allows me to get the standardized score from the age group and the raw score indicated. Someone told me about the equiv and index functions but I can't get it to work. Can someone help me please?

 

[Eric W]

Try this:

Book1 (version 1).xlsb
	A	B	C	D
41	Age of the patient	4-0 to 4-2
42	Raw score of the patient	4
43	Calcul of its standardized score	71
44
45
46
47
48
49
50
51	Raw Score	4-0 to 4-2	4-3 to 4-5	4-6 to 4-9
52	0	58	55	52
53	1	61	59	56
54	2	64	62	59
55	3	68	65	62
56	4	71	68	65
57	5	75	72	69
58	6	78	75	72
Sheet17

Cell Formulas
Range	Formula
B43	B43	=VLOOKUP(B42,A52:D58,MATCH(B41,A51:D51,0))

 

[robin11]

I had to translate it with the french formula but it works perfectly well !
This formula will probably me save me like 4 hours ! Thanks a lot dude, very appreciated

 

[Marc L]

Hi,​

​

in fact you do not ever need to translate the formula 'cause whatever the local version like your french one Excel is still in native english inside !​

On VBE side open the Immediate window (aka 'Exécution' in french version) then you can manually use some easy VBA statement​

like [B43].Formula = "=VLOOKUP(B42,A52:D58,MATCH(B41,A51:D51,0))" then validate via the Enter key…​