excel - Using Individual Columns to Map Data to a Table Array -

suppose have 2 columns, c1 , c2, each of contains real valued data. create two-dimensional table these columns each row specific range of values in c1 (e.g. 400-500) , each column specific range of values in c2 (e.g. 10-14). easy enough sorting c1 , c2 , determining reasonable ranges. key issue have third column c3 values fill table with. in particular, able select c3 , have check associated values in c1 , c2 , use information place value in appropriate cell @ intersection of 2 values of table. require vba?

if understand correctly, c3 has (c1, c2) coordinates. original coordinates in c1 number in matrix non-overlapping range, e.g., c1 can 413 falls under range 401-500 in new matrix. possible without vba using vlookup data mapping , transformation.

of course, method gets more , more complex, facilitate future reuse, amendments , readability, recommended use vba, simpler add comments , see algorithm @ work in continuous flow.

if want avoid using vba, derive range in c1 falls using vlookup range lookup, , combine range , c2 value 1 cell, separated comma, sort data in c3 new column in ascending order. note when using vlookup range lookup, give response, maximum value equal or less c1 value, tends not result in #n/a; you have make sure ranges cover possible values of c1.

assuming c1 ranges found in column a , c2 values found in row 1, use vlookup populate matrix, handle #n/a thereafter.

so have:

table of c3 values

• c1 values in column b
• c2 values in column c
• combined coordinates in column d in sheet
• resulting c3 values in column e

table of c1 ranges

• minimum value of range in column a
• actual range in column b

create table of c1 ranges

min val     range 0           0 1           1-100  101         101-200 201         201-300 301         301-400 401         401-500 501         501-600 etc. 

combine c1 , c2 coordinates column d. example, translates c1 = 413, c2 = 21 "400-500,21",

=vlookup(b2,tablec1ranges!$a$2:$b$100,2)&","&c2 

then sort column d , use vlookup in matrix of c1 , c2 values

=vlookup($a2&","&b$1,tablec3!$d$2:$e$1000,2,false)