Excel help - select cell value from cell based on other cell value.

[xrapidx]

Maybe someone can help me, I did this years ago - and can't remember how.

If I change the value in B1 from Team A to Team B - I want all the values in Team B (F) to be displayed in B under Active. (B).

Then again, if I change B1 to Team C (G) - I want all team C's values to display under Active (B).

*The image is a very basic example on a single sheet of what I'm trying to achieve.

 

[fluent]

Different ways of doing... try using IF()

=IF(B$1=E$4,E5,IF(B$1=F$4,F5,IF(B$1=G$4,G5,IF(B$1=H$4,H5))))

> Paste that into the cell below "Active"
> If that works, Copy formula into lower cells as well

 

[JazzeD]

Yeah as fluent says and for every team add another if() so for 3 teams it would be as below.

=IF(B1=E4,E5,IF(B1=F4,F5,IF(B1=G4,G5,FALSE)))

Not the cleanest but once setup it works.

 

[xrapidx]

Thanks - I got the nested if's, but isn't there a way to do it that's neater, e.g. a VLOOKUP?

 

[czc]

Thanks - I got the nested if's, but isn't there a way to do it that's neater, e.g. a VLOOKUP?

I don't know if there is a better way.

 

[SirFooK'nG]

use Hlookup

CELL B3=hlookup(b1;f2:h6,2)
CELL B4=hlookup(b1;f2:h6,3)
CELL B5=hlookup(b1;f2:h6,4)
CELL B6=hlookup(b1;f2:h6,5)

 

[LaraC]

VLOOKUP works well for data which is stored in columns. Your data is stored horizontally. In this case it is better to use HLOOKUP.
=HLOOKUP(B$1,E$4:H8,2)
The problem with this formula is that you have manually edit the row offset.

 

[xrapidx]

Lemme have a look, thanks.