Alternative lookup function

[dustinp]

Hello all, I’m trying return the value of column J in “weekly time list” to column B in worksheet “ryan”. Due to the duplicates in in both columns H & J of weekly time list the formula

=INDEX('Weekly Time list'!$J$2:$J$600,MATCH(ryan!$B$1639,'Weekly Time list'!$H$2:$H$600,0))

Doesn’t seem to be a viable solution. Is there a better way to write this formula to where I ignore all of the duplicates?

 

[Fluff]

Are you trying to get all the distinct names from col J where B1639 matches col H?

 

[dustinp]

Are you trying to get all the distinct names from col J where B1639 matches col H?

yes, that's exactly what I'm trying to do

 

[Fluff]

Ok, how about

=UNIQUE(FILTER('Weekly Time list'!$J$2:$J$600,'Weekly Time list'!$H$2:$H$600=$B$1639))

 

[dustinp]

unfortunately no, that formula gives me a #value! error

 

[Fluff]

Ok, what happens if you try this

=FILTER('Weekly Time list'!$J$2:$J$600,'Weekly Time list'!$H$2:$H$600=$B$1639)

 

[dustinp]

Ok, how about

=UNIQUE(FILTER('Weekly Time list'!$J$2:$J$600,'Weekly Time list'!$H$2:$H$600=$B$1639))

 

[Fluff]

What is the answer to my question?

 

[dustinp]

Ok, what happens if you try this

=FILTER('Weekly Time list'!$J$2:$J$600,'Weekly Time list'!$H$2:$H$600=$B$1639)

thank you so much! gives me exactly the the return I was looking for

 

[Fluff]

You're welcome & thanks for the feedback.