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

2018acway

New member
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.
 
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.
 
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
 

Attachments

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.
 

Attachments

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))

Lookup Values.png

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

1. Lookup Values.png

Download Excel file:
 

Attachments

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)
 
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.
 
Where is 1093 in the corresponding fin number and aircraft type?
Wrong Explanation.png

There is no fin number for the flight number 1093

1. Wrong Explanation.png

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?
 
Everyday flight numbers have a different fin number associated to them. So, I need the formula to pick up the correct aircraft type.
 
You can use this formula:
=VLOOKUP([@FIN],AB:AC,2)

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

Online statistics

Members online
0
Guests online
8
Total visitors
8

Forum statistics

Threads
363
Messages
1,589
Members
679
Latest member
KeviseRak
Back
Top