VLOOKUP strange behavior

[ezdmanx]

Can anyone tell me why this shows an incorrect value in the worksheet at the link below?

https://www.dropbox.com/scl/fi/t21ab6iiu2tiic829yy72/Prob.xlsx?rlkey=9nk2ks90bmj72lygrouoflr53&dl=1


I cannot figure it out.

 

[awoohaw]

What is the formula? I'm not really keen on clicking a link from a first time visitor to the forum, there is a phishing risk in that.
If possible please use the xl2bb add in to post a mini worksheet (Link below).

this formula could work:
in cell B2 and copy down:

=SUBSTITUTE(A2,"WT-","")

 

[Legacy 143009]

I don't know the reason why it is displaying the previous record if you have two records starting with the same letter(F-FL,FR)
But I know the solution. If you don't input the "matching type" argument in the formula, the default value will be TRUE, which means it will always make a close match. If you want to make an exact match, you must set it to FALSE(0).

=VLOOKUP($A2,Sheet2!$A$2:$E$13,2,0)

However I don't understand this behaviour in close match. Very strange.

 

[Fluff]

Because you are using an approximate match the data must be sorted ascending. If you change the order of the data on Sheet2 to this

Prob.xlsx
	A	B
1	Window Type	Type
2	WT-A	A
3	WT-B	B
4	WT-C	C
5	WT-D	D
6	WT-E	E
7	WT-FL	FL
8	WT-FR	FR
9	WT-G	G
10	WT-H	H
11	WT-J	J
Sheet2

It will work.

But as you have WT-FR before WT-FL it returns the value for WT-E.
HTH

 

[Legacy 143009]

But as you have WT-FR before WT-FL it returns the value for WT-E.

Hi @Fluff

Why close match behaves like this? What is the logic?

 

[Fluff]

WT-FR comes after WT-FL alphabetically & so vlookup will return the next lowest value to WT-FL which is WT-E