Vlookup with IF statement or other ideas?

[jberg123]

Imagine companies of two categories, A and B.
They have all different market values.
One column per company (categories mixed, ie. NOT the first x are A, rest B).

Row 1 gives market value
Row 2 gives category (A or B)
Row 3 gives rank within category (so there are two number 1s etc)

Now, on another sheet I have:

Category A
1
2
3
...
Category B
1
2
3
...


Next to the rank number I want the company name.

Just a vlookup on the rank won't help as I need the condition of the category.

The companies' category may change so it needs to be flexible.
Any ideas?

Totally lost. Was thinking about some VLOOKUP with IF statement in some CSE-function, but no luck.

Thanks

 

[Peter_SSs]

I've done this on one sheet, but certainly the same results could be achieved on a second sheet. This could be done without the helper cells, but to me this is an easy way to go. See if it is any use to you.

1. Add a new row 1 above the company names.
2. Formula in B1 copied across. (You could hide row 1 at this point if you want)
3. Formula in I2 copied across.
4. Formula in I4 copied across and down.

I assume that you already have the formulas/results for what is my row 5 since you haven't asked about that.

BTW, what version of Excel are you using?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		A1	B2	A2	A3	B1
2	Name:	Company 1	Company 2	Company 3	Company 4	Company 5			3	2
3	MV:	8	5	6	3	9		Category:	A	B
4	Category:	A	B	A	A	B		1	Company 1	Company 5
5	Rank:	1	2	2	3	1		2	Company 3	Company 2
6								3	Company 4
7								4
Company Rank

 

[jberg123]

Brilliant!!! Works very well.

Clever idea...

Working in 2003. Company shifting to 2007 in near future. Dreaing the moment...

 

[Peter_SSs]

Thanks.

It's probably a bit early for you then but in Excel 2007+ you can dispense with the formulas in I2:J2 and use this formula in I4 (across & down).

BTW, if your company could be persuaded to skip Excel 2007 and move straight to Excel 2010, I think most users would be happier.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		A1	B2	A2	A3	B1
2	Name:	Company 1	Company 2	Company 3	Company 4	Company 5
3	MV:	8	5	6	3	9		Category:	A	B
4	Category:	A	B	A	A	B		1	Company 1	Company 5
5	Rank:	1	2	2	3	1		2	Company 3	Company 2
6								3	Company 4
7								4
Company Rank (2007+)