ExcelBanter - View Single Post

smartin

wrote:
hi all

I am trying to write a UDF that can travel through a sorted table and
find a value based on 9 inputs.

For example. say the UDF name is MultiMatch. I will type in Page 2
Cell A1 " =Multimatch('Page1'!A1, AA1, AB1, AC1, AD1, AE1, AF1, AG1,
AH1) " all the inputs are cell addresses.
MultiMatch will start at 'Page1'!A1, and travel down column A until it
finds a cell with value equal to AA1 (the second input of MultiMatch),
in this case A30. It will then shift to the next column, B30, and
travel down column B until it arrives at a cell equal to cell AB1, in
this case B40. It then shift right yet again to C40, then travel down
column C to find cell with same value as AC1, which is C50, and then
shift right to column E yet again, and so on and so forth, until the
final column H was arrived where a cell has the same value as AH1, say
cell H100. Multimatch last returns the numerical value of the next
cell to the right of H100, which was G100 in our example.

I have written the below UDF, but I can't seem to make it work. Can
anyone look at this and help me out? I really don't want to use
concatenate all the keys then use VLookup. I know it works but its not
the way I want to do things for this model.

Thank you all

Are your key values unique across a row? If so, this worksheet function
might do the job.

I put sorted* data in $A$1:$C$16 and some values in column D. To return
the lookup on three columns (column A="B", column B=2, and column
C="x"), the following returns the correct value from column D:

=SUMPRODUCT(--($A$1:$A$16="B"),--($B$1:$B$16=2),--($C$1:$C$16="x"),($D$1:$D$16))

* It doesn't matter if the table is sorted.