LOOKUP Question

[WLMPilot]

I have the following problem and don't know if I need to find a different
function or what.

FltInfo!B12 = N48967 (the tail number of aircraft to be flown)
FltInfo!B13 = formula to find type aircraft on another worksheet based on B12.

You can have multiple tail numbers per type airplane.
Ex: Tail Numbers N48967, N12345, N23456, etc, could all be a Cessna 172XP
type.

The airplane performace specs (worksheet = Airplanes) w/ the associated tail
numbers are entered in columns C:G (for example) So specs and tail numbers
for a Cessna 172XP are in column C, with C40:C43 being the tail numbers. The
range for ALL tail numbers are C40:J43 and the type airplane is in C1:J1

I want to locate the tail number (based on FltInfo!B12) within the range
Airplanes!C40:J43 and return the value within range Airplanes!C1:J1 (type
airplane)

Thanks for you help!
Les

 

[slow386]

Sounds to me like an INDEX and MATCH together, but I'm having trouble
visualizing the sheet.
The formula below goes in a table from C12 to H17 and returns the value at
the intersection of columns D ~ H and rows 13 through 17

=INDEX(C12:H17,MATCH(N3,C12:C17,0),MATCH(N9,C12:H12,0))

HTH

 

[Jean-Guy]

Hi,

Maybe has something better but try this in the meantime:

=INDEX(Airplane!C1:J1,0,IF(ISERROR(MATCH(Fltinfo!B12,Airplane!C40:J40,0)),0,MATCH(Fltinfo!B12,Airplane!C40:J40,0))+IF(ISERROR(MATCH(Fltinfo!B12,Airplane!C41:J41,0)),0,MATCH(Fltinfo!B12,Airplane!C41:J41,0))+IF(ISERROR(MATCH(Fltinfo!B12,Airplane!$C42:J42,0)),0,MATCH(Fltinfo!B12,Airplane!C42:J42,0))+IF(ISERROR(MATCH(Fltinfo!B12,Airplane!C43:J43,0)),0,MATCH(Fltinfo!B12,Airplane!C43:J43)))

HTH
Jean-Guy

 

[WLMPilot]

I am trying to figure out what INDEX is doing. I do not know where the N3
and N9 reference is coming from that you put in there. I will try to paint a
better picture.

Airplanes Worksheet.
Columns C - J each have data pertaining to a particular type plane, ie
Cessna 172, Cessna 152, Cessna 172XP, etc. For this example I will use a
Cessna 172 in column C. C2 = Cessna 172. C3:C44 = various data pertaining
to planes specs, ie Fuel capacity, Landing/Takeoff Distance, Ground Roll, etc.

The section I am concerned with is C40:C43, D443, etc (for each plane). I
allow up to four separate tail numbers per plane. Similar to having the same
type car and four different license tags. To entire array for the tail
numbers is C40:J43, which would allow for eight different plane types
(models), and four tail numbers per plane type.

In worksheet FltInfo, the user inputs information about the flight he will
be flying. The user will enter the tail number of the plane in B12. In B13,
I want to look at
entire are of tail numbers (C40:J43) and find the tail number entered. When
found, I want FltInfo!B13 to hold the value of the plane type that
corresponds with the tail number. The answer will be in C2:J2.

Hope that helps,
Thanks,
Les

 

[Conan Kelly]

WLMPilot,

Reading your discriptions of your table, and the other responses, I'm not
sure if what you are asking is feasible (keep in mind that I'm not an
expert, compared to these other guys.......my knowledge of XL is
intermediate+).

It would make things a WHOLE lot easier if you changed your lookup table
into 2 lookup tables: 1st table would be tail number and type of aircraft,
2nd table would be type of aircraft and all the stats that go with each
type.

Then in FltInfo!B13, you could lookup the type of aircraft from the tail
number using the first table, and then in FltInfo!B14:B... you could look up
aircraft type specs from FltInfo!B13 (aircraft type) using the 2nd table (if
that is what your goal is).

HTH,

Conan