Multiple reference lookup return one value

[Scawpio]

Hi,

I have a large data set over 12k lines that has multiple duplicate employee references. Eg 5 John Smiths 12345 One column is of interest that shows there eg cell B1 cost centre, however there are numerous cells that are blanks and some with values.

Is there a formula i can use to find John Smith 12345 , and cell b1 returning the value not yhe blank?

I have tried a vlookup but it returns the blank but I want it to only return the value not any blanks.

Thanks?

 

[jtakw]

Hi,

Does this help:

Book3.xlsx
	A	B	C	D	E
1	John Smith			Name	Result
2	Jane Doe	34567		John Smith	12345
3	John Smith	12345		Jill Smith	23456
4	Jack Ryan
5	Jill Smith
6	John Smith
7	Jill Smith	23456
Sheet1044

Cell Formulas
Range	Formula
E2:E3	E2	=LOOKUP(2,1/((A$1:A$7=D2)*(B$1:B$7<>"")),B$1:B$7)

 

[Scawpio]

Thanks for that... I'll see if this works. The cell I have is employee ID, and the cell a want has multiple cells with 1 value the rest is blank. So will try and see of it works if I tweak it slightly

 

[jtakw]

You can show samples with columns and cell references.

 

[Scawpio]

Thar worked!!! I swapped the cell reference over to start with employee I'd and find cost centre and that worked

Thank you so much

 

[jtakw]

You're welcome, thanks for the feedback.