Find a textstring in a named range and return the corresponding value from a column

[Tresfjording]

I want to search the named range "percent", and return the value in "branches".

Whatever I try with, xlookup, isnumber, index, match or countif, it just won't work

Not even Copilot solved this.

What am I doing wrong?

 

[Sektor]

Could you elaborate more on this? Any examples of what you're doing?

 

[Tresfjording]

mrexcel find cellvalue.xlsx
	E
2	#N/A
Sheet1

Cell Formulas
Range	Formula
E2	E2	=XLOOKUP(INDEX(percent,MATCH(D2,percent,0)),percent,C4:C11)

Named Ranges
Name	Refers To	Cells
Branches	=Sheet1!$C$4:$C$11	E2
percent	=Sheet1!$D$4:$O$11	E2

 

[AhoyNC]

You need to supply more info.
It would help if you could show a small sample of your data and expected results.
What's in your percent range?
What's the value in D2?
what's in cells C4:C11?

We have no idea what your data looks like and only showing a formula the gives an #N/A is of no real use.

 

[Tresfjording]

I am sorry abut that, but I really haven't a clue on how to attach a worksheet to a message.
I have downloaded and installed the Excel addon XL2bb, and uploaded a mini sheet, but that wasn't very useful, if I understand you right.
How can I attach a worksheet to a message here?
I figure there is a stupid and easy way that I have overseen.

Just in case, here is a link to the file: mrexcel find cellvalue.xlsx
Thanks for your time and effort.
Tresfjording

 

[Cubist]

Looking up numbers can be tricky. Is the number you're looking up match exactly in the percent range? They might look the same rounded but the stored value may not. Or are you looking for the next closest match?

 

[Fluff]

How about

=INDEX(Branches,AGGREGATE(15,6,ROW(Branches)-MIN(ROW(Branches))+1/(ROUND(percent,6)=D2),1))

 

[Tresfjording]

How about

=INDEX(Branches,AGGREGATE(15,6,ROW(Branches)-MIN(ROW(Branches))+1/(ROUND(percent,6)=D2),1))

Thank you very much!!
Works perfect!

 

[Fluff]

You're welcome & thanks for the feedback.