Re: Formula for Horizontal and Vertical Search Combination

Tech-Archive recommends: Fix windows errors by optimizing your registry

On 2/26/08 1:43 AM, in article ee8d0cb.1@xxxxxxxxxxxxxxxxxxxxxxx,
"budspwr@xxxxxxxxxxxxxxxx" <budspwr@xxxxxxxxxxxxxxxx> wrote:

Bob, thanks for your reply. I never know if anyone ever answers these
questions.

I have tried several versions of your suggestion but without success.

Will you do this for me? Write out the suggested formula both independently
and combined.

Here's a table example: in column A list several parts. In row B1 through J1,
put a single (same) company. In row B2 through J2, list years 2002 through
2010. In cells B3 though J# (in my test ***, I'm using 3 rows so # = 5)
insert several and variable prices. In Column K3 though K5, I have MIN(B3:J3),
etc. In column L3 through L5, I want to know the year. In column M3 through
M5, I want to know the company.

There will be a dozen or so such tables and I will want to know a single
source of a product at the lowest cost and in what year said product was
purchased in each row of items in column A.

The following are formulas I've used:
INDEX(array,row_num,column_num) returns the value of a specified cell or array
of cells within array.
INDEX(reference,row_num,column_num,area_num) returns a reference to specified
cells within reference.
MATCH(lookup_value,lookup_array,match_type)

MATCH(MIN(B3:J3),MAX(B2:J2),0), I get an #N/A
You are getting an error because the second term in the above returns a
single value, not an array.

Thanks again

Bud
Your explanation is not at all clear to me. Send me an example work*** and
explanation of what you are trying to find and I'll help.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

.

Quantcast