Values not displaying using INDEX MATCH or LOOKUP formula

[excelnewbielearning]

Hello again,

Having some issues displaying my values.

One sheet has information such as a product id, date of booking, hours booked and its available amount left along with other information.

The second sheet is the availability sheet - this is where I compare the product id, date and hours to show how much of the product is available for that time (number between 0-1).

I have provided an example file of what my problem is, it might help clarify what iv tried.

I tried two formulas based on previous help from here, but I think my referencing or order might be wrong. Please help in any way...not sure where I have gone wrong.

Tried this formula: =IFERROR(INDEX(Table1[Available], MATCH(C$4 & $B5 & $B$1, Table1[Dates] & Table1[Hours] & Table1[[ID ]], 0)), "")

Also tried this formula: =IFERROR(LOOKUP(2,1/('Sheet 1'!$C$2:$C$21=$B5)/('Sheet 1'!$B$2:$B$21=C$4)/('Sheet 1'!$A$2:$A$21=B$1),'Sheet 1'!$G$2:$G$21),"")

Best

 

[NARAYANK991]

Hi ,

There doesn't seem to be any problem.

Narayan

 

[Naresh Yadav]

Hi,

Pls try this one and finish the formula with CTRL+SHIFT+ ENTER
you should use the formula adding "|" with your & function so that formula can read all product id, date and hours separately

=IFERROR(INDEX(Table1[Available],MATCH($B$1&"|"&C$4&"|"&$B5,Table1[[ID ]]&"|"&Table1[Dates]&"|"&Table1[Hours],0)),"")

 

[excelnewbielearning]

Hi ,

There doesn't seem to be any problem.

Narayan

Oh that's great, Thanks.

I'm just wondering because in my actual file I'm having problems displaying some values,
for example, for product id 3 i have these 3 dates with times from 6-10, on my actual file it displays the values for 6-8, but does not display the values in 9-10, even though they have an availability amount in the column.

ID DATE HOUR
3 18/09/2017 06:00
3 18/09/2017 07:00
3 18/09/2017 08:00
3 18/09/2017 09:00
3 18/09/2017 10:00
3 19/09/2017 06:00
3 19/09/2017 07:00
3 19/09/2017 08:00
3 19/09/2017 09:00
3 19/09/2017 10:00
3 20/09/2017 06:00
3 20/09/2017 07:00
3 20/09/2017 08:00
3 20/09/2017 09:00
3 20/09/2017 10:00

 

[NARAYANK991]

Hi ,

You need to check the data to see whether the times are exact times where the seconds are 0. The cell format is hh:mm and without looking at your data , we cannot say whether there are any seconds values or not.

Narayan

 

[excelnewbielearning]

Hello,

Iv had a look at my 'hour' field, both on sheet 1 and sheet 2 set to custom hh:mm. Is there anything else I should be checking for?

Best,

 

[excelnewbielearning]

Hi,

Pls try this one and finish the formula with CTRL+SHIFT+ ENTER
you should use the formula adding "|" with your & function so that formula can read all product id, date and hours separately

=IFERROR(INDEX(Table1[Available],MATCH($B$1&"|"&C$4&"|"&$B5,Table1[[ID ]]&"|"&Table1[Dates]&"|"&Table1[Hours],0)),"")

Thank you, this works, but im still having the same problem of values for times 9-10 not showing up.

 

[excelnewbielearning]

Hello

Just thought I let you know it works now. I don't know what the problem was exactly, but I deleted those product details from my first tab and then inserted a new row with the same details. It seemed to work after that.

Best wishes,