Find value based on specific text demands

**smide** · 01-19-2017, 06:59 AM

Hello.

In columns A and B (A2:B802) I have a list of 25 different products (Product 1 - Product 25) with their corresponding values in columns C and D.

When Product is in column A his value is in the same row in column C, and when Product is in column B his value is again in the same row but now in column D.

This in an example of my data format.

	A	B	C	D	E	F
1			Price A	Price B	Results A	Results B
2	Product 1	Product 3	12	9
3	Product 4	Product 6	6	11
4	Product 6	Product 1	14	23
5	Product 3	Product 4	2	19
6	Product 5	Product 3	13	25
7	Product 1	Product 4	9	25

It's a bit complicated so I'll try to explain in a few simple (at least I hope so) steps.

For each product I need to:

1) identify the position (row number) of next product within columns A and B, Product with same text
- for example, for Product 1 in cell A2 next Product 1 is in row 4 (cell B4)

2) after identifying the row number I need to find which Product is in the same row with Product from 1) (Product 1 in this case)
- for example, after identifying row 4 as row of next Product 1 obviously Product 6 is also in the row 4, so Product 6 (in this case) is 'wanted' Product

3) then I need to find PREVIOUS position of that 'wanted' Product (PREVIOUS Product 6 is in row 3 , cell B3 in this case), corresponding price of that PREVIOUS Product 6 is equal to 11 (cell D3) and I need to place that value as the result in cell E2 (in the row of Product 1 from which all of this started)
if text 'Product 1' was in cell B2 instead of cell A2 than value 11 should be placed in cell F2 of course

4) and FINALLY IF 'wanted' Product from 2) is in column A (which is the case here) 20 should be added on corresponding value from 3) - so FINAL value in cell E2 here is 11+20 = 31

Here is the calculation example (for simplicity only for Product 1 here).

	A	B	C	D	E	F
1			Price A	Price B	Results A	Results B
2	Product 1	Product 3	12	9	31
3	Product 4	Product 6	6	11
4	Product 6	Product 1	14	23		19
5	Product 3	Product 4	2	19
6	Product 5	Product 3	13	25
7	Product 1	Product 4	9	25

Already explained value 31 in cell E2, F4 = 19, next Product 1 (after that in row 4) is in the row 7 , 'wanted' Product is Product 4 (cell B7), PREVIOUS Product 4 is in the row 5 with corresponding value in cell D5 = 19 (Product 4 in row 7 is in the column B so nothing should be added here eg. without adding 20) .

**Richard Buttrey** · 01-19-2017, 07:03 AM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Please don't upload pictures alone, they are rarely much use without the workbook to work with.

**smide** · 01-19-2017, 08:18 AM

Originally Posted by Richard Buttrey

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Please don't upload pictures alone, they are rarely much use without the workbook to work with.

Here is the example workbook. Hope it's not too complicated...
(please check 'BEFORE' sheet first)