Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

hollyj · May 11th 2015

I have two worksheets in excel my first sheet looks like this showing staff names and skills numbered from 1-5. The numbers represent the level of skill they have in that area of work.e.g Holly Jenkinson has a skill level 4 for tailings and skill level 3 for water.

[TABLE="width: 500"]

[tr]

[td][/td]

[td]

a

[/td]

[td]

b

[/td]

[td]

c

[/td]

[td]

d

[/td]

[td]

e

[/td]

[td]

f

[/td]

[/tr]

[tr]

[td]

5

[/td]

[td][/td]

[td]

Hydraulics

[/td]

[td]

Tailings

[/td]

[td]

Water

[/td]

[td]

Hydrology

[/td]

[/tr]

[tr]

[td]

6

[/td]

[td]

Name

[/td]

[td]

Surname

[/td]

[td][/td]

[/tr]

[tr]

[td]

7

[/td]

[td]

Holly

[/td]

[td]

Jenkinson

[/td]

[td]

1

[/td]

[td]

4

[/td]

[td]

3

[/td]

[td]

5

[/td]

[/tr]

[tr]

[td]

8

[/td]

[td]

Tim

[/td]

[td]

Newman

[/td]

[td]

0

[/td]

[td]

2

[/td]

[td]

2

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

9

[/td]

[td]

Louise

[/td]

[td]

Cooper

[/td]

[td]

1

[/td]

[td]

5

[/td]

[td]

4

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

10

[/td]

[td]

Lee

[/td]

[td]

Lynch

[/td]

[td]

4

[/td]

[td]

2

[/td]

[td]

3

[/td]

[td]

5

[/td]

[/tr]

[tr]

[td]

11

[/td]

[td]

Elaine

[/td]

[td]

Fisher

[/td]

[td]

0

[/td]

[td]

0

[/td]

[td]

2

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

12

[/td]

[td]

Mark Dillon

[/td]

[td][/td]

[td]

1

[/td]

[td]

5

[/td]

[td]

4

[/td]

[td]

0

[/td]

[/tr]

[/TABLE]

my second sheets looks like this.The idea is that cell B2 and B3 are drop down boxes containing all the firstnames and surnames from sheet one.Cells B5:F5 represent the skill levels numbered from 1-5.

[TABLE="width: 500"]

[tr]

[td][/td]

[td]

a

[/td]

[td]

b

[/td]

[td]

c

[/td]

[td]

d

[/td]

[td]

e

[/td]

[td]

f

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td][/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

Firstname

[/td]

[td]

Holly

[/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

Surname

[/td]

[td]

Jenkinson

[/td]

[td][/td]

[/tr]

[tr]

[td]

4

[/td]

[td][/td]

[/tr]

[tr]

[td]

5

[/td]

[td]

Skill

[/td]

[td]

5

[/td]

[td]

4

[/td]

[td]

3

[/td]

[td]

2

[/td]

[td]

1

[/td]

[/tr]

[tr]

[td]

6

[/td]

[td][/td]

[/tr]

[tr]

[td]

7

[/td]

[td][/td]

[/tr]

[tr]

[td]

8

[/td]

[td][/td]

[/tr]

[tr]

[td]

9

[/td]

[td][/td]

[/tr]

[tr]

[td]

10

[/td]

[td][/td]

[/tr]

[/TABLE]

I want to search using the surname and skill level in sheet one and return the name of the skills in sheet 2 e.g If I select Jenkinson in cell B6 want to select all the skills she has at level 5. Then in cell C6 all the skills at level 4 etc etc. Therefore in cell B6 I should return values Hydrology and in cell C6 I should return values tailings.

azumi · May 11th 2015

Re: Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

If I understand you correctly, put this on B6 on Sheet2:

=IFERROR(INDEX(Sheet1!$C$1:$F$1,SMALL(IF((Sheet1!$C$3:$F$8=B$5)*(Sheet1!$A$3:$A$8=$B$2)*(Sheet1!$B$3:$B$8=$B$3),COLUMN(Sheet1!$C$3:$F$8)-COLUMN(Sheet1!$C$3)+1),1)),"-")

and press F2 button so you'll see the formula, after that press/hit CTRL+SHIFT+ENTER button/key all together, ENTER alone not working, if success you'll see bracket like this "{formula}".
This called array formula, final step copied cross as necessary

hollyj · May 12th 2015

Re: Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

Hello, That works great, thanks so much.

If it's not too much trouble what can I add to the formula to find all the values for example Tim Newman has a level 2 skill level for Tailings and Water.

azumi · May 12th 2015

Re: Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

Ignore this

azumi · May 12th 2015

Re: Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

Please see this, hope this helps, all is array formulas.........

hollyj · May 12th 2015

Re: Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

forum.ozgrid.com/index.php?attachment/65185/

Thanks for the help its is very much appreciated. I'm afraid I need the data to be kept in columns so all level 4 skills will be displayed vertically underneath the level 4 heading and all level 3 skills under the level 3 heading etc.

For example Holly Jenkinson might have three skills in level 5. I would like them displayed vertically as below. I have done this before when searching rows and returning values horizontally using the small and if function. I am little confused as now I'm searching columns and returning values vertically.

[TABLE="width: 500"]

[tr]

[td]

Skill

[/td]

[td]

5

[/td]

[td]

4

[/td]

[td]

3

[/td]

[td]

2

[/td]

[td]

1

[/td]

[/tr]

[tr]

[td][/td]

[td]

Water

[/td]

[td][/td]

[td]

Hydraulics

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Tailings

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Hydrology

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]

I have done something similar before when searching rows and returning values horizontally using the small and if function. I am little confused as now I'm searching columns and returning values vertically.

azumi · May 12th 2015

Re: Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

Could be like this?

hollyj · May 13th 2015

Re: Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

GREAT THANKS that work perfectly. I really appreciate all your help!!!

Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup

Participate now!

Share