Nested IF function? If X & IF Y, THEN = Z

**jammy78** · 01-12-2012, 10:10 AM

Hi all,

I need some help with a IF function?

I have a database which is populated from a form for booking a computer desk. So eachtime a booking is made, it adds to the DB. So all the old booking will still be there.

Columns in Database:

Desk (Column A)
Date Booked FROM (J)
Date Booked TO (K)
STATUS (O) = "Free", "Booked" or "Free Today but future booking"
OLD BOOKING = "Y" or "N" (i.e. has the Date Booked TO past)

Columns in Summary:
Desk (Column A) ..... 1-42
STATUS (B) = need to work this out

So I need a formula to search column A looking for the corresponding Desk Number, that is not an old booking = to give the status.

Hopefully this makes sense & will be simple for you guys & girls!

Thanks

**NBVC** · 01-12-2012, 10:19 AM

Try something like:

=INDEX(Sheet1!$O$2:$O$100,MATCH(1,INDEX((Sheet1!$A$2:$A$100=A2)*($P$2:$P$100="N"),0),0))

where Sheet1 is database sheet, and range goes to row 100... and P2:P100 is the "OLD Booking" column

adjust as necessary and copy down.

**jammy78** · 01-12-2012, 10:51 AM

Originally Posted by NBVC

Try something like:

=INDEX(Sheet1!$O$2:$O$100,MATCH(1,INDEX((Sheet1!$A$2:$A$100=A2)*($P$2:$P$100="N"),0),0))

where Sheet1 is database sheet, and range goes to row 100... and P2:P100 is the "OLD Booking" column

adjust as necessary and copy down.

Thanks for the quick reply!!!!

I'm getting #N/A? So I need to use the INDEX & MATCH Functions....... I'll see if I can work it out!?

**NBVC** · 01-12-2012, 10:52 AM

If you need further help, post sample workbook.

**jammy78** · 01-12-2012, 04:50 PM

Here's a sample of the spreadsheet......

**NBVC** · 01-13-2012, 08:22 AM

The formula would be:

=INDEX(Database!$O$2:$O$23,MATCH(1,INDEX((Database!$A$2:$A$23=A2)*(Database!$P$2:$P$23="N"),0),0))

this gives #N/A if there is no match for the Desk # at the same time having a "N" in column P....

**jammy78** · 01-16-2012, 05:14 AM

Originally Posted by NBVC

The formula would be:

=INDEX(Database!$O$2:$O$23,MATCH(1,INDEX((Database!$A$2:$A$23=A2)*(Database!$P$2:$P$23="N"),0),0))

this gives #N/A if there is no match for the Desk # at the same time having a "N" in column P....

Thanks for you help, it's much appreciated!