Manifest & Seating Plan Formula Help

[kenan01210]

Hi all,

I am having trouble finding the correct formula for my attached problem, I need to find a formula that has a set of conditions for a seating plan, I want the values in column L6 to change based on the gender of a person M - Male, F - Female, C - Child, I - Infant based on where they have been allocated by seat in J6 - J81 in pax manifest sheet as passengers will not always seat in order from the start of the column down. 

After that I want the value to auto populate in the seat plan based position 1A followed by which gender it positioned in the corresponding sheet. 

If someone could please help me out that would be great cause this is stressing me out, sorry if it sounds like an easy fix but I am still learning.

Thank you. 

[#2]

@kenan01210 

Hi,

If you have office365, I think you can use XLOOKUP to do this. (I made up some random names in the attached example).

 

[#3]

@rachel 

Thank you so much for your help, I managed with what you have supplied me the exact formulas i needed and even learnt how to write them up myself, thank you so much!

I just got one problem, in column M & N of the below sheet, I have made it so that as I allocated seats in column J it highlights the seat e.g. 1A and changes colour to green to indicate the seat has been taken. The only problem is the formula comes up as #N/A when I only want the column to change colour once the seat has been assigned but I have tried xlookup and vlookup and can't seem to figure it out. For instance 6A us #n/a but i want the seat number to remain and just the column change colour. 

Could you please assist in helping with this issue just one more time, thank you. 

Pax Manifest Template.xlsm

[#4]

@kenan01210 

Glad to hear that you find XLOOKUP formulas straightforward!

I updated the formula in column M, IFERROR replaces "#N/A" with blank.

I also updated the conditional formatting. if cell in column M is not blank (i.e seat is taken), then the cell turns green: