Need help with INDEX/MATCH or VLOOKUP for multiple criteria across multiple sheets.

ahk4713

New Member
Joined
Jul 24, 2014
Messages
1
Hello all,

I am currently working on a pretty big Excel file (2007 on Windows XP work computer) for my medical institution and have come into a scenario that seems a bit too complex for me.

My workbook is being used to input the normal weights and measures of children based on their age and sex. Currently, the workbook includes Sheet 1 titled "Autopsy Form-Both Sides"; from there I have 14 sheets that separate normal means and standard deviations for different organs, their height, and their weight based on their ages and sex.

Sheet names:
Male Day Organs
Female Day Organs
Male Month Organs
Female Month Organs
Male Year Organs
Female Year Organs
Male Weight 0-3
Female Weight 0-3
Male Weight 4-18
Female Weight 4-18
Male Length 0-3
Female Length 0-3
Male Length 4-18
Female Length 4-18

The only information that I will input is on Sheet 1 and there are three criteria I need it to match before finding the answer. There is one blank for the number age (P4), one for the text age (Q4), and one for the sex (R4) that I need Excel to look up and match with the tables on the other sheets.

For example, cell F9 will be the normal body weight for the child. I will type: 4 (P4), 'year (Q4), 'M (R4). The normal body weights for 4-18 yo males are found in Sheet 6, 'Male Weight 4-18', and looks like this:

Age (yr)
Weight (M + SD)
4
17 ± 3.3
5
18.5 ± 4
6
20.5 ± 4.5
7
23 ± 5.8
8
25 ± 7
9
28 ± 8.8
10
31.5 ± 10.5
11
35 ± 12.3
12
39.5 ± 14.3
13
45 ± 16
14
50.5 ± 17
15
56.5 ± 18
16
62 ± 19
17
66.5 ± 19.8
18
69 ± 20.8

<TBODY>
</TBODY>
*Note, all of this information is formatted as a table. Not sure if that is an issue or not.

With the help of whatever formula that we end up using, I need the final answer in cell F9 on Sheet 1 to populate as 17 ± 3.3.

As a final problem I may run in to, the male and female "day" tables are not separated into individual cells but look like this:

Age (Days)
Brain
Liver
Heart
1
277.1 ± 68
68.8 ± 5.1
13.2 ± 1.2
2-30
383.3 ± 34.5</SPAN>
140.1 ± 15.1</SPAN>
25.4 ± 4.6</SPAN>
31-60
474.5 ± 42.7
148.4 ± 27.2
26.4 ± 4.5
61-90
595.3 ± 51.7</SPAN>
186.4 ± 15.1</SPAN>
27.4 ± 2.7</SPAN>
91-120
614.1 ± 42.9
176.1 ± 17.2
30.4 ± 6.2
121-150
665.2 ± 77.5</SPAN>
215.5 ± 33.9</SPAN>
28.3 ± 2.2</SPAN>
151-180
713.8 ± 61.7
269.1 ± 56.3
38 ± 6.5

<TBODY>
</TBODY>

Will Excel be able to pick these ranges out or should I input each day separately so that it is easier to look through?

Any help would be greatly appreciated!
-Amanda
 

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top