I am looking for the value in cell B20, based on the following:
Vertical ranges are designated as Member, Allowable, Actual, Ratio and Case for the first five columns. Input cells are G3, G4 and G5.
The member value in G3 must be on the same line as the case value in G4. I then want the value in range Allowable in the same row as the value in G5.
The sheet will have maybe 10,000 rows of data.
I was thinking of using a combination of Index and Match, but can’t get there.
Yes, I need to learn VBA, just haven’t gotten to it yet.
A B C D E F G
1 Member Allowable Actual Ratio Case
2
3 C3020 30 25 0.2 1601 C3021
4 E2-1 31 26 0.3 1602
5 F1-1 32 27 0.4 F1-1
6 F2-1 33 28 0.5
7
8 C3020 34 29 0.21 1602
9 E2-1 35 30 0.22
10 F1-1 36 31 0.23
11 F2-1 37 32 0.24
12
13 C3020 38 33 0.211 1603
14 E2-1 39 34 0.221
15 F1-1 40 35 0.231
16 F2-1 41 36 0.241
17
18 C3021 42 37 0.1 1602
19 E2-1 43 38 0.11
20 F1-1 44 39 0.12
21 F2-1 45 40 0.13
Any help is appreciated!
I attached the file as I failed to copy the data correctly.
Bookmarks