Iferror with nested/multiple if and vlookup

[ArchieV]

Hi Guys,
Appreciate if somebody can help me correct below formula:

=IFERROR(IF(VLOOKUP(G770670,Aircrafts!$A$2:$E$1000,1,FALSE)CL650,IF(VLOOKUP(G770670,Aircrafts!$A$2:$E$1000,2,FALSE)G5000,IF(VLOOKUP(G770670,Aircrafts!$A$2:$E$1000,3,FALSE)G6000,IF(VLOOKUP(G770670,Aircrafts!$A$2:$E$1000,4,FALSE)G7000,IF(VLOOKUP(G770670,Aircrafts!$A$2:$E$1000,5,FALSE)Airbus),"Others")

What I'm trying to do is:

In a cell in column X (X770670), I will input the above formula. It will use the input in cell G770670 as basis to identify what type/model of aircraft was in that cell.
But will have to use VLOOKUP as the reference for all aircraft numbers and to what model they belong is in sheet/tab Under the name of "Aircraft".

Basically the table contains as follows:

Column A - all aircraft numbers under CL650 model
Column B - all aircraft numbers under G5000 model
Column C - all aircraft numbers under G6000 model
Column D - all aircraft numbers under G6000 model
Column E - all aircraft numbers under G7000 model

If the formula was able to find the aircraft numbers on each column specified, it will display the aircraft model (CL650 or G5000 or G6000 or G7000 or Airbus) in cell X770670. Or else if it can not find the specific model for an aircraft number, it will show "Others".

It's been 2 days now that I am working on this and checking online but I ca not seem to get the proper formula.

Would appreciate any assistance.

 

[BobUmlas]

=IFERROR(VLOOKUP(G770670,Aircrafts!$A$2:$A$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$B$2:$B$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$C$2:$C$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$D$2:$D$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$E$2:$E$1000,1,FALSE),"Other")))))

 

[ArchieV]

=IFERROR(VLOOKUP(G770670,Aircrafts!$A$2:$A$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$B$2:$B$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$C$2:$C$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$D$2:$D$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$E$2:$E$1000,1,FALSE),"Other")))))

Bob, first I wanna thank you for taking the time to help. Appreciate it.
The formula was able to identify the aircraft number in the table found in Aircraft sheet.
However, it is copying the actual aircraft number in X770670 cell. Is there a way that we can add in formula a way to determine the aircraft type?
I've added some détails on your formula below:

1st VLOOKUP - CL650
2nd VLOOKUP - G5000
3rd VLOOKUP - G6000
4th VLOOKUP - G7000
5th VLOOKUP - AIRBUS

=IFERROR(VLOOKUP(G770670,Aircrafts!$A$2:$A$1000,1,FALSE)CL650,IFERROR(VLOOKUP(G770670,Aircrafts!$B$2:$B$1000,1,FALSE)G5000,IFERROR (VLOOKUP(G770670,Aircrafts!$C$2:$C$1000,1,FALSE)G6000,IFERROR(VLOOKUP(G770670,Aircrafts!$D$2:$D$1000,1,FALSE)G7000,IFERROR(VLOOKUP (G770670,Aircrafts!$E$2:$E$1000,1,FALSE)AIRBUS,"Other")))))

In my table found in "Aircraft" sheet:
Column A - contains all CL650 aircraft numbers
Column B - contains all G5000 aircraft numbers
Column C - contains all G6000 aircraft numbers
Column D - contains all G7000 aircraft numbers
Column E - Contains all AIRBUS aircraft numbers

Example, when the formula identified the aircraft number written in cell G770670 (cell G770670 is found in "Data" sheet) and found it in the table under Column A (table is in "Aircrafts" sheet), it will write or show the word "CL650" in cell X770670 (cell X770670 is found in "Data" sheet) since all CL650 aircraft numbers are listed Under this column.
If the encoded aircraft number in cell G770670 is located in Column B, it will display the word "G5000" in cell X770670.
If the encoded aircraft number in cell G770670 is located in Column C, it will display the word "G6000" in cell X770670.
If the encoded aircraft number in cell G770670 is located in Column D, it will display the word "G7000" in cell X770670.
If the encoded aircraft number in cell G770670 is located in Column E, it will display the word "AIRBUS" in cell X770670.
And if it doesn't find the encoded aircraft number in cell G770670 in all the columns in table located in "Aircrafts" sheet, it will display the word "Others" in cell X770670.

Would really appreciate any input or idea you can offer.

 

[ArchieV]

=IFERROR(VLOOKUP(G770670,Aircrafts!$A$2:$A$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$B$2:$B$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$C$2:$C$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$D$2:$D$1000,1,FALSE),IFERROR(VLOOKUP(G770670,Aircrafts!$E$2:$E$1000,1,FALSE),"Other")))))

Hey Bob, I found this formula which works only on a single IF, ISNA & VLOOKUP statement:

=IF(ISNA(VLOOKUP(G770670,Aircrafts!$A$2:$A$1000,1,FALSE)),"Others","CL650")

However, when I expanded it to use multiple ISNA & VLOOKUP, it doesn't work.

=IF(ISNA(VLOOKUP(G770670,Aircrafts!$A$2:$A$1000,1,FALSE)),"Others","CL650",ISNA(VLOOKUP(G770670,Aircrafts!$B$2:$B$1000,1,FALSE)),"Others","G5000",ISNA(VLOOKUP(G770670,Aircrafts!$C$2:$C$1000,1,FALSE)),"Others","G6000",ISNA(VLOOKUP(G770670,Aircrafts!$D$2:$D$1000,1,FALSE)),"Others","G7000",ISNA(VLOOKUP(G770670,Aircrafts!$E$2:$E$1000,1,FALSE)),"Others","AIRBUS")