[Solved] Lookup Values from Large Dataset based on Multiple Criteria

[2018acway]

I have a formula issue, and I need your help.
I attached a sheet. In sheet 2 you'll see aircraft reg's and fin numbers in the first two colums. The fin numbers in yellow are duplicates. It only means the fin is a duplicate and not the reg. I need a way to have a formula pick the correct fin number.
So, lets say AC main line operates mostly with flight numbers under 1000, and Jazz operates in the 7000 to 8000 range. I need a formula to pic the correct fin.

 

[shamimarita]

Hello 2018acway,

May be mistakenly sheet is not attached. Without the sheet, it's hard to provide a formula solution.

However, based on the description, I can suggest a general approach.

You are trying to match the correct fin number based on the flight number range (e.g., AC mainline operates under 1000 and Jazz operates in the 7000 to 8000 range). In that case, you can use the nested IF statements in Excel.

Formula:
=IF(AND(A2 < 1000, A2 > 0), "AC Mainline", IF(AND(A2 >= 7000, A2 <= 8000), "Jazz", "Other"))

This formula will check if the flight number (assumed to be in cell A2) falls within AC Mainline or Jazz ranges and assign the corresponding fin number. You would need to adjust the formula based on the specific logic you require and where the data is located in the sheet.

 

[2018acway]

Sorry about that, I thought i did.

So, If i enter a mainline flight number and enter a fin the is duplicate. the formula should pick up the correct fin number

 

[2018acway]

Sorry for the confusion.

I have attached another file. I'm looking for a formula/ vlookup to help me out. When I enter a flight number and a fin. I need the formula to pickup the aircraft type. The problem is that some aircraft fins are duplicates. so, i would need to formula to choose the correct aircraft type based off the flight number.

 

[shamimarita]

Hello 2018acway,

You can use the INDEX-MATCH or XLOOKUP function to look up aircraft type based on multiple criteria. You can change the criteria based on your need.

=INDEX(H:H, MATCH(1, (J:J=A2)*(G:G=B2), 0))



=XLOOKUP(1, (J:J=A2)*(G:G=B2), H:H)



Download Excel file:

 

[2018acway]

I noticed that when i change the flight number in A2, I get error message

 

[shamimarita]

A2 is not used in the formula how will it impact the formula to show error?

Your formula is incorrect:
=INDEX(AC:AC, MATCH(1, (AE:AF=G4)*(AB:AB=I4), 0))

Correct Formula will be:
=INDEX(AC:AC, MATCH(1, (AE:AE=G4)*(AB:AB=H4), 0))

Flight Number 377 doesn't contain any AirCraft Type so the formula will return #N/A as value is not available.

You have a very large dataset to lookup . XLOOKUP will be good fit for the large dataset
=XLOOKUP(1, (AE:AE=G4)*(AB:AB=H4), AC:AC)

 

[2018acway]

Thank you for your continuous assistance. I know your time is valuable. I tried your formula and I still get the N/A. I entered flight 1093 with fin number 471. the result should come up as a 321. But it doesn't.

 

[shamimarita]

Where is 1093 in the corresponding fin number and aircraft type?


There is no fin number for the flight number 1093



I request you to format your dataset properly and then use the formula to lookup. If value doesn't exist in the dataset then how can formula return it?

 

[2018acway]

Everyday flight numbers have a different fin number associated to them. So, I need the formula to pick up the correct aircraft type.

 

[shamimarita]

You can use this formula:
=VLOOKUP([@FIN],AB:AC,2)


Insert the flight number manually as the flight number is not aligned with the fin number and aircraft type.

 

[2018acway]

Thank you very much for all your help.

 

[shamimarita]

You are most welcome. I hope your problem is solved now.