INDEX MATCH MATCH formula to check availability

[excelnewbielearning]

Hello there,

I will try and explain my excel problem as best I can.

I have a table on one sheet with information such as a product id, date of booking and its status along with other various information.

On a second excel sheet, I have an availability sheet - this is where I need to compare the product id and date to show what the status for that product is (reserved, booked, out of charge, available).

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

I tried to use a INDEX, MATCH, MATCH formula, but i think im going wrong somewhere.
Please help in anyway.

=INDEX(Table1[[#All],[Status]],MATCH(A4,Table1[[#All],[ID ]],0), MATCH(C3,Table1[[#All],[Dates]],0))

Best

 

[bosco_yip]

In C4, copied across and down :

=IFERROR(LOOKUP(2,1/('Sheet 1'!$A$2:$A$20=$A4)/('Sheet 1'!$B$2:$B$20=C$3),'Sheet 1'!$H$2:$H$20),"")

p.s. Don't use Table range A2:H1048576, it was too large and will slow down your computer.

The Table range can apply to used range, it will grow up while the data increased.

Regards
Bosco

 

[excelnewbielearning]

Thank you @bosco_yip

That is brilliant :D Works great. I would never have thought to use a lookup.

 

[NARAYANK991]

Hi ,

If you do not mind using an array formula , you can use :

=IFERROR(INDEX(Table1[Status], MATCH(C$3 & $A4, Table1[Dates] & Table1[[ID ]], 0)), "")

This is an array formula , to be entered using CTRL SHIFT ENTER.

However , more to the point , a table is never used with extra , blank rows.

The whole purpose of using a table is that all formulae within the table , and external to the table which reference columns within the table , will always be valid , irrespective of any data that may be added to the table.

That is the reason structured referencing never refers to worksheet cell addresses ; instead , we use column names , so that whether a table contains one row of data or a million , the formula will always work without the user having to do anything.

Narayan

 

[excelnewbielearning]

In C4, copied across and down :

=IFERROR(LOOKUP(2,1/('Sheet 1'!$A$2:$A$20=$A4)/('Sheet 1'!$B$2:$B$20=C$3),'Sheet 1'!$H$2:$H$20),"")

p.s. Don't use Table range A2:H1048576, it was too large and will slow down your computer.

The Table range can apply to used range, it will grow up while the data increased.

Regards
Bosco

Hello Bosco,

I was wondering, in the LOOKUP formula, you look up the value 2 and then the next argument has 1/...etc. What is the significance of looking up 2 and then having 1/? Thank you

Best,

 

[mu4you]

In C4, copied across and down :

=IFERROR(LOOKUP(2,1/('Sheet 1'!$A$2:$A$20=$A4)/('Sheet 1'!$B$2:$B$20=C$3),'Sheet 1'!$H$2:$H$20),"")

p.s. Don't use Table range A2:H1048576, it was too large and will slow down your computer.

The Table range can apply to used range, it will grow up while the data increased.

Regards
Bosco

Some times I feel Bosco is robot or from different world..... MASTER of Masters