Xlookup against hyperlink number in cell

[rrmando18]

Hello party people. Is there a formula that would allow me to xlookup the job number based on the hyperlink PO number? The PO Number will always be a hyperlink. The list is much longer than the sample below. Xlookup returns blank when I try a regular xlookup formula. I ended up using a helper column where I convert the hyperlink PO number to text and I'm able to lookup against the helper column. Is there a formula option without the helper column? Thank you for your time.

Job Number	PO Number
250003	4502736874
250004	4502709556
250005	4502734008
250004	4502733967
250006	4502733569
250007	4502724744
250005	4502735319
250008	4502737156

 

[SunnyAlv]

if u need helper column to convert the hyperlink cell, so try to change the hyperlink form in the formula like this :

=XLOOKUP(VALUETOTEXT(H2),B2:B9,A2:A9)

' Change PO Format to "Text"

=XLOOKUP(H2,NUMBERVALUE(B2:B9),A2:A9)

' Change Hyperlink Format to "Number"

because the Hyperlink format is “Text”, so you change the PO Number format to “text”, or vice versa change the hyperlink to “Number” format

 

[rrmando18]

if u need helper column to convert the hyperlink cell, so try to change the hyperlink form in the formula like this :

=XLOOKUP(VALUETOTEXT(H2),B2:B9,A2:A9)

' Change PO Format to "Text"

=XLOOKUP(H2,NUMBERVALUE(B2:B9),A2:A9)

' Change Hyperlink Format to "Number"

because the Hyperlink format is “Text”, so you change the PO Number format to “text”, or vice versa change the hyperlink to “Number” format


View attachment 123446

The VALUETOTEXT did the trick without having to use the helper column. Thank you so much!

 

[SunnyAlv]

happy to help