CODEWITHSUNDEEP
excel
DeathGrip
DeathGrip

Reputation: 171

Excel spreadsheet check two conditions along different rows & columns

Hey I'm really stuck on this one, basically tying to get a result of either 'hired' or 'available' (or 0 & 1 to represent) in B2, of BOTH the two conditions in B1 & A2, looking at the log table A6:B10 of when they are 'hired out'. I've tried VLOOKUP and many IF functions but neither quite work correctly.

Spreadsheet example

Upvotes: 1

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

One option here, though perhaps not the most graceful, would be to use VLOOKUP with the car and date as the key. The issue here is that there are multiple lookup values, namely the car and the particular date. To get around this, you could create a new column C which contains the concatenated car and date, e.g.

      A  |     B      |        C         | D
 6 Car 1 | 03-01-2017 | Car 1 03-01-2017 | 0
 7 Car 2 | 03-01-2017 | Car 2 03-01-2017 | 0
 8 Car 3 | 04-01-2017 | Car 3 04-01-2017 | 0
 9 Car 4 | 05-01-2017 | Car 4 05-01-2017 | 0
10 Car 2 | 06-01-2017 | Car 2 06-01-2017 | 0

To create column C, simply enter the following formula into cell C6 and then copy it down the entire column:

=CONCATENATE(A6, " ", B6)

Now you can use the following VLOOKUP formula in cell B2 to calculate whether a given ride is available on a certain date:

=IFERROR(VLOOKUP(A2&" "&B1,C6:D10,2,FALSE), 1)

Here I have hard-coded in column D the value 0 for every entry, to represent that these are rides which are already hired-out for that particular car and date. If our VLOOKUP formula finds a match, then it means the ride is hired-out. If VLOOKUP does not find a match, it would throw an error, in which case we display 1 to indicate availability.

Upvotes: 1

Related Questions