I need a formula to search between two variables

[jdiogocf]

hoteis.jpg

I have this output list that has in column A the name of the hotels and in column I the dates of the check out, in the column G you have the rates for that especific date, room, and hotel.

hoteis2.jpg

I want to have the lowest value of the column G, to a given hotel and specific date. So in D6 should appear 88 because is the lowest value from Hotel Fenix Lisboa for day 20/01/2016

I hope you can help, thanks in advance.

[shukla.ankur281190]

Try

D3=INDEX(Output!$G$2:$G$1000,SMALL(IF(Output!$I$2:$I$1000=Final!$D2,IF(Output!$A$2:$A$1000=Final!C$3,ROW(Output!$B$2:$B$1000)-ROW(Output!$B$1))),ROWS(Final!$C$2:C2))) with CRTL+SIFt+ENTER and drag down.

[jdiogocf]

The formula works fine if I drago right, If I drag down it shows error !NUM

[shukla.ankur281190]

Oopss it just because reference lock

try

=INDEX(output!$G$2:$G$1000,SMALL(IF(output!$I$2:$I$1000=final!D$2,IF(output!$A$2:$A$1000=final!$C3,ROW(output!$B$2:$B$1000)-ROW(output!$B$1))),ROWS(final!$C$2:C2))) with CTRL+SHIFT+ENTER

[jdiogocf]

I changed but the samething happened

[jdiogocf]

There is any other option?

[shukla.ankur281190]

Can you upload your workbook??

For uploading workbook go to advance and click on attachment link and attach the workbook

Thanks Ankur

[jdiogocf]

Booking Scraper_v2.xlsm

Thanks in advance!

[shukla.ankur281190]

Try
D3=IFERROR(INDEX(Output!$G$2:$G$15000,SMALL(IF(Output!$I$2:$I$15000=Final!D$2,IF(TRIM(Output!$A$2:$A$15000)=TRIM(Final!$C3),ROW(Output!$B$2:$B$15000)-ROW(Output!$B$1))),1)),"This date is not in Output Sheet") with CTRL+SHIFT+ENTER

Ps- If Date is not in output sheet it will show massage, you can change it as you want.

check the attachment,

[jdiogocf]

Works perfect, thank you very much

[shukla.ankur281190]

Glad to help you. If your problem now solved, please mark thread as solved ( Please see the text below the signature how to do it).

If you satisfy with my solution please add rep by clicking star * icon left corner on your screen on that post which you help you

[jdiogocf]

I'm sorry but the formula it's note finding the lowest value. If you look in the output sheet the lowest value for that given hotel at 23/01/2016 is 75 but in the Final sheet shows 78,5

Booking Scraper_v2 - Copy.xlsm

[shukla.ankur281190]

It seems I did wrong thing previously.....

Try

D3=IFERROR(SUBSTITUTE(MIN(IF(Output!$A$2:$A$52=Final!$C3,IF(Output!$I$2:$I$52=Final!D$2,SUBSTITUTE(Output!$G$2:$G$52,",",".")+0))),".",","),"Sem Preço") with CRTL+SHIFT+ENTER

For confirmation that you did the right key stroke you will see { } before and after you formula....

Hope this will help you

[jdiogocf]

Almost perfect, the only problem is that now I dont get decimal numers. Instead of 78,75 I get 7875

[shukla.ankur281190]

Is you decimal looks like "," or "." ???

[jdiogocf]

that is the problem, doesnt appear the decimal, so instead of having 75,78 or 75.78 i have 7578

[shukla.ankur281190]

Ok Try

=IFERROR(MIN(IF(Output!$A$2:$A$52=Final!$C3,IF(Output!$I$2:$I$52=Final!D$2,SUBSTITUTE(Output!$G$2:$G$52,",",".")+0))),"Sem Preço")

[jdiogocf]

Appears "Sem preço" There is nothing that we can change in the last formula to give me decimal prices? because besides that, the formula works good

[shukla.ankur281190]

I don't know what is happening there.

but it works for me.

See the attachment.