Excel guru needed

[BLK306]

Ok so I'm trying to figure out how to write a formula for an excel document I use at work. Let me see if I can type this so it makes sense. I'm dealing with aircraft tail numbers and thier parking location. On one tab is all of the tail numbers in a cell with their parking location in the cell directly below them as such

0229
1

0222
8

etc.

All of the tail numbers and parking locations are in the same collum. In my case it is B6 through B86. The aircraft tail numbers are always in the same cells. The parking locations however, will change often. We change the parking locations frequently so one day parking location 1 may be shown in cell B8 then later after some aircraft are moved around on the flightline parking location 1 will then show in say B17 for example.

What I need to do is have a cell on a different tab search cells B6-B86 on the tabe with all of the tail numbers and parking locations for a cell that only has the number 1 or 2 or whatever number I want it to search for. Then when it finds that number I need it to look at the cell above it which will be the tail number and enter that number in to the cell.

Does anyone out there have any idea how to do that?

P.S.
I'm using Excel 2010

[Sean88gt]

Nassty Nate knows his way around excel.

[slow99]

Just glancing, sounds like a vlookup, no?

[BLK306]

Just glancing, sounds like a vlookup, no?

Just watched a youtube video on Vlookup and it appears that it only works when looking for a value on the same row not in the same column. Now it may be possible and I just don't know how to tweek it to work but tha is what I saw on the video.

[slow99]

Just watched a youtube video on Vlookup and it appears that it only works when looking for a value on the same row not in the same column. Now it may be possible and I just don't know how to tweek it to work but tha is what I saw on the video.

Just noticed same column - use an hlookup.

[Nash B.]

Use the indirect() and match() functions:

=INDIRECT("TailSheet!B"&(MATCH(<target value>,TailSheet!B6:TailSheet!B86,0))+4)

where TailSheet is the sheet with the tail #s and parking spots, <target value> is the parking spot you're looking for, B6:B86 is the range to search, and 4 is the offset from the top of the sheet to the beginning of the range - 1 (since you're looking for the tail number above the parking spot)

[BLK306]

Use the indirect() and match() functions:

=INDIRECT("TailSheet!B"&(MATCH(<target value>,TailSheet!B6:TailSheet!B86,0))+4)

where TailSheet is the sheet with the tail #s and parking spots, <target value> is the parking spot you're looking for, B6:B86 is the range to search, and 4 is the offset from the top of the sheet to the beginning of the range - 1 (since you're looking for the tail number above the parking spot)

Let me give this one a shot. I did tweek around with the vlookup and got it to work. Let me see which one is easier.

My next quest will be to see if I can get multiple tail numbers to show under the same parking location. For example we may have severl airplanes in the hanger. However we show no disctiction on our seet. Every airplane that is in the hanger just shows HGR.

[BlackGT]

Would a simple "if-then" formula not work?

[Nash B.]

At some point, a database will start to be a better fit.

[BLK306]

Use the indirect() and match() functions:

=INDIRECT("TailSheet!B"&(MATCH(<target value>,TailSheet!B6:TailSheet!B86,0))+4)

where TailSheet is the sheet with the tail #s and parking spots, <target value> is the parking spot you're looking for, B6:B86 is the range to search, and 4 is the offset from the top of the sheet to the beginning of the range - 1 (since you're looking for the tail number above the parking spot)

Alright I entered it just like this and got the #N/A error

=INDIRECT("STATUS!B"&(MATCH(11,STATUS!B6:STATUS!B8 6,0))+4)-1

[BLK306]

Would a simple "if-then" formula not work?

It may I just don't know how to write it. Now I'm far from an excel guru so it may be super easy I just have not been able to figure it out with google and youtube yet. LOL

[BlackGT]

Try this...
On Tab 2 in whatever cell you want
=IF('Tail# Park Spot'!B2=1,'Tail# Park Spot'!B1)

Where
'Tail# Park Spot'! = the name of your first tab.
B2 = park spot cell
B1 = tail number cell
You can copy and past the formula into as many cells as you need, and also modify it to pick up other parking spot numbers and their tail numbers...

[BLK306]

Try this...
On Tab 2 in whatever cell you want
=IF('Tail# Park Spot'!B2=1,'Tail# Park Spot'!B1)

Where
'Tail# Park Spot'! = the name of your first tab.
B2 = park spot cell
B1 = tail number cell
You can copy and past the formula into as many cells as you need, and also modify it to pick up other parking spot numbers and their tail numbers...

This one will not work because it assumes that the parking loctions does not change and the tail number is changes. When in fact it is the opposite. The tail number is always in the same cell and the parking locations changes cells. Let me try and post a screen shot and see if it helps everyone understand what I'm trying to do.

[BlackGT]

All you have to do is have a column for each parking #.

Column B is for parking spot 1
Column C is for parking spot 2

Take the formula and copy to column C and change B2=1 to B2=2
See screen shots below
Only bad thing about this way is you will have blank cells if the value doesn't match for that given cells formula...

[BLK306]

Ok this first picture shows the tail numbers and parking location which is found on the first tab (Status).



This second picture shows what I'm trying to do with the data. I want to illustrate the ramp so that each parking location shows which tail number is parked on that spot.



Now as I mentioned earlier I was able to get the VLOOKUP to work. However once I linked cell A1 on the secon tab (picture) to B8 on the first tab (Status) the formula would no longer work. So something about have linked data makes the formula wet the bed.