Hi would like some assistance please.

On sheet 1 I have prepared a list of passenger names with seats numbers in the following column each in individual cells eg 1A, 6B etc

EG column 1 passenger name - B SMITH, column 2 seat No - 12A

On sheet 2 I have a seat map with rows 1A 1B 1C 1D etc through to row 19A B C D each seat number in a seperate cell.

What I would like to do is have the cell on sheet 2 reflect the number 1 if the seat is occupied and if the seat is empty then the cell on sheet 2 would be empty too.

So in the above example sheet 2 would have the number 1 in the cell on sheet 2 which represents 12A, all othwr cells would be empty.

Hope this makes sense. Thanks in anticipation. Debb


Assuming seat numbers are in column A of sheet 2, you could put this formula in column B of sheet 2:


The MATCH formula is checking for seat number (in column A of sheet 2), against seat numbers in column B of sheet 1. If it doesn't find it, the error #N/A will occur, so the IFERROR replaces those errors with blank. Otherwise, you should get value of 1 if seat number is found.

Replace Sheet1 with the actual name of your worksheet containing the passenger names.

| improve this answer | |
  • Sorry pressed enter in error for this message – user646942 Sep 30 '16 at 20:15
  • Thanks so much for this formula, it was very helpful and the closest I've come to finding a solution, only problem is that the cell on the seat map that needs the number 1 in it is returning the line number from the passenger list. Any suggestions? Thanks again. – user646942 Sep 30 '16 at 20:16
  • Sorry had my test data in row 1 so it was returning 1. Forgot MATCH would return position. This adds another layer, checking if the cell would be "" after error test for match; if it is then leave it as "", otherwise we've found a match so set value to 1: =IF(IFERROR(MATCH(A1,Sheet1!B:B,0),"")="","",1) – Tanya Oct 1 '16 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.