About Us

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to search a column that is not the first of the table	Antonio	Excel Worksheet Functions	4	October 24th 06 09:24 PM
need to search a table such as distance table in road maps.	Ronnie	Excel Discussion (Misc queries)	3	July 6th 06 01:42 PM
How do i build a search table in excel	Obi-Wan Kenobi	Excel Worksheet Functions	2	March 20th 06 03:20 PM
search from a data table	appz	Excel Discussion (Misc queries)	1	March 16th 06 02:22 PM
search value in a table and sum another column	a931048	Excel Worksheet Functions	3	September 14th 05 04:50 AM

Menu

About Us