Match or Lookup multiple approximate values from a table

**fasih** · 02-19-2009, 11:14 AM

I apologize if this is not right place to ask this question. Also i went through couple of existing thread on MATCH/INDEX, but doesn't seem to solve my problem.

Here is my sample data

Test A Test B Test C Result
1 1 1 A
1 2 2 B
2 1 1 c
2 2 2 D

Based on input values like Test A=1, Test B=2, Test C=1, I would like to see either A or B which is almost nearer since the actual match doesnt exists. I tried this formula, but guess something is wrong with it.

=INDEX(D1:D5,MATCH(1,(A1:A5="1")*(B1:B5="2")*(C1:C5="1"),1))
or
=INDEX(D1:D5,MATCH(1,(A1:A5="1")*(B1:B5="2")*(C1:C5="1"),-1))

It would be great, if someone can help me out in getting the result.

Thanks for your time

**NBVC** · 02-19-2009, 12:02 PM

I think it would be easiest if you set up a side table that shows all possible combinations in one column...and then the associated letter in the next column, then you can use Vlookup()

e.g..

If you set up a table, where the 3-digit combos are in one cell and the letter in the next...

111 A
112 B
113 C
121 D
122 A
etcc.

then

=Vlookup(A1&B1&C1,$X$1:$Y$100,2,0)

would lookup the combo made up of A1&B1&C1 in the column range X1:X100 and extra the letter from the coinciding cell in Y1:Y100.