Show Value for Match w/2 Cell Conditions

In my example booklet. I've got two sheets.
Sheet 1 has 9 inputs (rows) from 5 sources: A,B,C,D,E (columns)
Sheet 1 also has flag columns to match inputs based upon similiar values (names) in description columns.

Sheet 2 - has two tables,
first table matches values of different sources w/ similiar descriptions
onto the same row.

table two: displays, min. value for each row in first table.

In the last column (P) of table 2, I'd like to have the descriptions (from sheet1) for the min value being displayed in the table(sheet2), Values for the Descriptions will come from one of these columns, C,L,U,AD,AM , depending
on min value from either columns E,N,W,AF,AO on sheet1 or min value
being display in table 1 or 2 on sheet 2. I don't know what information
is easier to reference.

The two conditions to consider would be: the value in the flags column for the source with the lowest value (from sheet1) ,is display on the row where the equivalent flag value is found on Sheet2 ,columnA

and description of the item with min value be >0.

I think a formula would have to incorporate both match and If functions.
How, I don't know.
Can anyone help me with a formula that will do this?

tia,

bdb

Is there a way to express the formula below that works?
The code alone will not work.

Please Login or Register  to view this content.

If the preceeding, is shortened and it's included into a match where, values in column A sheet matches value in either columns I,R,AA,AJ,AS, a min value

then, the value, I need would be in the column offset 6, from the cell in either columns I,R,AA,AJ,AS , and on the same row.

I know all this sounds crazy , but I don't know how else to describe the value I'm seeking.

Maybe simplier terms would be to say

First seek MATCH
Sheet2 columnA with Sheet1, columns E,N,W,AF,AO
, Then find Min Value from,
(columns I,R,AA,AJ,AS),
when found then show value in 4 cells to the left from cell w/ min value

The formula would be in each cell of column P of sheet2 where columnA of
sheet2 has a value to match.

any help would be much appreciated.

thanks,

bdb

I have to confess I'm struggling to follow.... as is would the below at least be returning the correct results for P5 based on the values already present (assume fixed for now)

Please Login or Register  to view this content.

It seems however that the rows in the INDEX may need to be dynamic... but to understand that I need to understand the spreadsheet and on that front I must confess that I am struggling.... it seems you're trying to list Sheet1 on Sheet2 on the basis of column I on sheet1, is that correct ?

If so I suspect you could simplify Sheet2 Column B...

Please Login or Register  to view this content.

Let us know.

Wow, That's pretty close to what I need in Column P.

Only problem is, the row of value from sheet 1 is also determined
by the values in columns I,R,AA,AJ,AS.

I made a mistake in my statement below which probably lead to your confusion.

, Then find Min Value from,
(columns I,R,AA,AJ,AS),

This is the correct logic/method:

Find, all values in (columns I,R,AA,AJ,AS) Sheet1
matching values in (columnA) Sheet2.

From matching values,
Find Min Value (4 cells to left) of matching values.

From min values
Find value (2 cells to left).

Results should be in column P of sheet2 of each row with matching value.

Any help appreciated.

Thanks,

bdb

Why don't you post up another sample file that reflects where you are now and more importantly detail your "expected results" - this may alleviate some of the confusion... I couldn't really follow the initial upload and there were no expected results listed so it was difficult to understand exactly what you were trying to achieve...

As it stands it all sounds a little convoluted... nothing in life is this complex ;-)

So out of idle curiosity does this work ?

DonkeyOte,

GollieeeGEEWizzzz, I think you got !!!!!!!
I've played around with it a little and it appears to be working!

----Awh Oh,

One thing, needs to be done, Has to work only If MIN VALUES are >0.
It is giving back #N/A for blank cells that corresponds w/ 0, or blank prices.

Soooooooooooooo close.

bdb

bdb1974, reply as a new post if you want people to be notified - I looked at this thread by accident and saw your edit... otherwise I'd not have come across it.

I don't understand where your #NA values are coming from - the file I provided shouldn't generate any such values... I painstakingly went through and reworked your old formulae to remove them from the output sheet... if you're not using the formulae I provided I'm afraid I can't help.

If the issue is in Column I - ie that Client should return Client for MIN value in C:H excluding values <=0 then adapt such that:

Please Login or Register  to view this content.

J:P should update accordingly.

You say "it's" not working but again this is not the same file as used previously, for example column I on Sheet2 (listing client) has disappeared and this was used elsewhere -- it's very hard when there's no consistency in the files with each question...

On a more pertinent note you don't explain why the results as detailed in R should be as listed... but based on my interpretation of your present formulae in C:H I suspect the below works for you:

Please Login or Register  to view this content.

On an aside, as mentioned before I think, I would alter the MIN formulae in J:O such that you do not require arrays:

Please Login or Register  to view this content.

On a final note given my new interpretation of what you're doing I'm not sure what value B on Sheet2 holds ... surely you need a column B for each column C:G given in reality B only relates to column C (seemingly).

Why should P5 on Sheet2 be "Yellow E" (as per Correct answers in R) ... the lowest value for Rank 0 in C:H is 1, the value 1 belongs to group "A" ... 0 for group "A" appears in I4 on sheet1 ... the corresponding value associated with I4 is C4 is it not, thus the correct answer should be "Orange A".

If the above is not the case then at this point I concede I can't help further because I can't follow your logic.

The last file doesn't even use the formulas I gave you on the last post... I'm going to withdraw from this thread I'm afraid as I'm tired of repeating myself over and over both in terms of my narrative and my solutions...

My last post on this as I note I inadvertently referenced row 2 on my last formula post rather than 5.

First clear all formulae from J5:P56

Copy & Paste the below into Sheet2!J5:

Please Login or Register  to view this content.

Copy the formula and apply to J5:O56


Copy & Paste the below into Sheet2!P5

Please Login or Register  to view this content.

Copy & paste formula across range P5:P56


None of the above formulae are arrays therefore they do not require CTRL + SHIFT + ENTER