Xlookup in 2 directions issue

[axelg]

I'm using a table like this

In cell B3 and B5 I have some calculated values which I pick up in other sheets.

B3, gives the value "=Invul_LOA", celname that is filled in on onther sheet. Basically that is always a number that i lookup in "Overal length" column.
B5, gives the value "=Result_DockPilot_RigthIn", calname that is calculated on other sheet. This is were I have the issue I believe. The value here is to be looked up in "Overal length" row (A,1,2,3A,3b,4,5)


The formula I use is : =XLOOKUP(B3;B16:B57;XLOOKUP(B5;C15:I15;$C$16:$I$57);0;1)

I cannot get it to work correctly? If in B5 there is a letter, it works. if a number, it doesn't (when I ad value(b5) in the formule, the number works , but the letters don't ???


Seems it has to do with where I pick up these B5 Values. Because if I manually type ( A or number ) then it works fine.
So maybe something in the layout of where i pick up the B Values ? But how can I get that correct?

Many thansk

 

[Fluff]

The easiest solution would be to change the values in C15:I15 so that they are all text.

 

[axelg]

The easiest solution would be to change the values in C15:I15 so that they are all text.

I do agree, but his is a sheet delivered not by me and is updated (pricelist) regulary. So as they work with this mix of characters and numbers...

The base sheet where I do the lookup for Cell B5 looks a bit like this one

So there you get which "tariff" you need to use. (That's whats in row 15)

 

[Fluff]

Ok, try it like

=XLOOKUP(B3;B16:B57;XLOOKUP(B5;C15:I15;$C$16:$I$57;XLOOKUP(B5+0,C15:I15,C16:I57));0;1)

 

[axelg]

XLOOKUP(B3;B16:B57;XLOOKUP(B5;C15:I15;$C$16:$I$57;XLOOKUP(B5+0,C15:I15,C16:I57));0;1)

What does that do exactly ? Why 3 times the xlookup in there?

 

[Fluff]

Does it work?

 

[axelg]

Does it work?

Curently at work So will try this or tomorrow evening.

 

[Fluff]

Ok

 

[axelg]

Ok

Seems to work Will do some furthe testing but seems ok in first test ... Yippiee
No idea what I'm doing, so please enlighten me, why the 3time Xlookup with in the last the cell+0 ?

 

[Fluff]

The 3rd xlookup is in the "if not found" argument of the 2nd xlookup, so if B5 is a number stored as text & isn't found then it will effectively do the same lookup but adding 0 to B5 which will turn it into a proper number.