lookup with two values

[Sara_Bertomeu]

Hi,

Trying to search the value from another table with two conditions.

I copy differents datas from calendars in one called STATUS.

With each Item I get all the information that I need.  

the number One proceeds from the original data, but more of the datas are hidden. I really obtain datas because the formulas works for me. But really the main information it's the book number, or número de reserva in Spanish.

And this only can obtain when I copy a manual invoice from CSV in the table from number 2, with automation.

And with line called 3, I copy again the data to the google calendar to have the availability in each room, but in this case I cannot add any extra information because is gelded.  

One time explain the situation, the question it's how can I obtain the book number from the table "BK" that match the check in and 

Sorry if i can't explain it better.

I try to use, but doesn't works :

AND(LOOKUP([Fecha inicio/hora], "STATUS", "ID_ROW", "Web Link"),LOOKUP([HAB], "STATUS", "ID_ROW", "Web Link"))

Sara

[daniel_sanchez · Accepted solution]

Prueba con esta formula:

ANY(

    SELECT(

        Status[Web Link],

        AND(

            [ID_ROW] = [_THISROW].[ID_ROW],

            [HAB] = [_THISROW].[HAB]

        )

    )

)

[Steve · Accepted solution]

You have:

TEXT([Fecha inicio/hora], "dd/mm/yyyy") = [_THISROW].TEXT([Check-in], "dd/mm/yyyy")

It should be:

TEXT([Fecha inicio/hora], "dd/mm/yyyy") = TEXT([_THISROW].[Check-in], "dd/mm/yyyy")

Notice where the second occurrence of TEXT() should be. 

[Steve]

---

@Sara_Bertomeu wrote:

AND(LOOKUP([Fecha inicio/hora], "STATUS", "ID_ROW", "Web Link"),LOOKUP([HAB], "STATUS", "ID_ROW", "Web Link"))

---

Please explain in plain language what you would want to accomplish with this expression if it worked the way you want it to. Feel free to explain in your native language.

[Sara_Bertomeu]

Gracias Esteve, trato de conseguir con dos valores un resultado.

Dato 1- BUSCA LA FECHA [Fecha inicio/hora] +

dato 2-[HAB] ,

en la tabla "STATUS",

donde "ID_ROW" sea el mismo "ID_ROW" que en la tabla BK,

Y obtén el "Número de reserva".

Cuando tenga este dato busca:

"web Link".

[Steve]

I'm afraid I just don't understand what you're trying to accomplish. 😞

[Sara_Bertomeu]

Creo que no puedo explicar lo better!! 🙄

Maybe when my Spanish angel cames from holidays!!!!!

[daniel_sanchez · Accepted solution]

Prueba con esta formula:

ANY(

    SELECT(

        Status[Web Link],

        AND(

            [ID_ROW] = [_THISROW].[ID_ROW],

            [HAB] = [_THISROW].[HAB]

        )

    )

)

[Sara_Bertomeu]

Pues me equivoqué, no funciona.

Use:

ISBLANK([Web Link]),HYPERLINK(ANY(SELECT(Status[Web Link],
AND( [HAB] = [_THISROW].[HAB 1], TEXT([Fecha inicio/hora], "dd/mm/yyyy") = [_THISROW].TEXT([Check-in], "dd/mm/yyyy"), [CALENDARIO ORIGEN] = [_THISROW].[CALENDARIO DESTINO] ) ) ), "2BK"))

Parecía que sí pero no consigo que enlace los datos de los tres campos de la tabla STATUS.

Pero mi error fue que en la tabla BK indica la fecha pero las horas son diferentes y entonces no da el resultado.

Entonces, trato de unir cosas:

ISBLANK([Web Link]),HYPERLINK(ANY(SELECT(Status[Web Link],
AND( [HAB] = [_THISROW].[HAB 1], TEXT([Fecha inicio/hora], "dd/mm/yyyy") = [_THISROW].TEXT([Check-in], "dd/mm/yyyy"), [CALENDARIO ORIGEN] = [_THISROW].[CALENDARIO DESTINO] ) ) ), "2BK")), así me dice que NO, EL TEXTO [_THISROW].TEXT([Check-in], "dd/mm/yyyy").

Quito [_THISROW]

IFS(
ISNOTBLANK([Web Link]),HYPERLINK([Web Link], "VER"),

ISBLANK([Web Link]),HYPERLINK(ANY(SELECT(Status[Web Link],
AND(

[HAB] = [_THISROW].[HAB 1],

TEXT([Fecha inicio/hora], "dd/mm/yyyy") = TEXT([Check-in], "dd/mm/yyyy"),

[CALENDARIO ORIGEN] = [_THISROW].[CALENDARIO DESTINO] ) ) ), "2BK"))

y PARECE QUE SÍ FUNCIONA. no entiendo muy bien porque pero!!!!!😅

[Sara_Bertomeu]

Gracias, parece que funciona, mil gracias. 

Thanks 

[Steve · Accepted solution]

You have:

TEXT([Fecha inicio/hora], "dd/mm/yyyy") = [_THISROW].TEXT([Check-in], "dd/mm/yyyy")

It should be:

TEXT([Fecha inicio/hora], "dd/mm/yyyy") = TEXT([_THISROW].[Check-in], "dd/mm/yyyy")

Notice where the second occurrence of TEXT() should be. 

[Sara_Bertomeu]

Always thanks, I understand now!!! It was the order.  ahha!!!!

You make it difficult , jajajjaja, but I continue!!!!😋

[Sara_Bertomeu]

ohhhhh NOOOOOOOO WORKS😥

IFS(
ISNOTBLANK([Web Link]),HYPERLINK([Web Link], [CALENDARIO DESTINO]),

ISBLANK([Web Link]),HYPERLINK(ANY(SELECT(BK[Web Link],
AND( [HAB 1] = [_THISROW].[HAB], TEXT([Fecha inicio/hora], "dd/mm/yyyy") = TEXT([_THISROW].[Check-in], "dd/mm/yyyy"), [Book Number]= [_THISROW].[Número de reserva] ) ) ), [CALENDARIO DESTINO]),

ISBLANK([Web Link]), ""
)

[Steve]

We came up with:

TEXT([Fecha inicio/hora], "dd/mm/yyyy") = TEXT([_THISROW].[Check-in], "dd/mm/yyyy")

Try this instead:

TEXT([_THISROW].[Fecha inicio/hora], "dd/mm/yyyy") = TEXT([Check-in], "dd/mm/yyyy")

Put [_THISROW] in front of [Fecha inicio/hora] rather than [Check-in].

[Sara_Bertomeu]

no works

[Steve]

That is an entirely different expression. You're on your own.

[Sara_Bertomeu]

You are right, like always. Works in initial value.

I'm going to check the automatations. Thanks Again. 

[Sara_Bertomeu · Accepted solution]

Solved!!!! Thanks @Steve  and @daniel_sanchez , you help so much you think. 

TEXT([Fecha inicio/hora], "dd/mm/yyyy") = TEXT([_THISROW].[Check-in], "dd/mm/yyyy")

this is the right!!! I was wrong like amost always