Lookup or Index Formulas

Dear Sir,

In a “worksheet 1” I have 5 columns, on the column B I have various country names eg. “Portugal” in a few cells, on the column D I have different Aircraft Types eg. “A319, A320, E120, etc” but none of them are in order…

In a “worksheet 2” I have on a cell A2 the Reference “Portugal” and on the column B, I want to sort down all the different Aircraft Type in “Worksheet 1 Column D” who have the same reference in its Column B.

I’ve tried different formulas like: Lookup, vlookup, index but they’re working.

Can you help please,

Kind Regards

Captura de ecrã 2015-08-7, às 08.54.41.png

This is the worksheet where I need to solve the problem...

Attachment 412279

Atachement not working

This is error message:

PHP Code: 

vBulletin Message
Invalid Attachment specified. If you followed a valid link, please notify the administrator 


- try to reattech by standard method:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Captura de ecrã 2015-08-7, às 09.14.22.png

Please find attach herewith the picture

Are you unable to attach a sample spreadsheet as Kaper asked? Pictures are not of much help. To upload a workbook click go advanced then use the manage attachments button midway down the screen. You'll get more help that way.

Business Portfolio.xlsx

Dear All,

On the attached file I'm trying solve the problem on the sheet "Income Statement" D76:87 and the reference is on B73 "Mozambique".

I want fill fill down on cell D76:87 the information on the Sheet "Aircraft" with the Tab in Blue, Column D if they have the same reference as B73 - Income Statement, in Column B - Aircraft

I've tried the following formula but didn't work out as well:

=INDEX(AIRCRAFT!$B$2:$D$32;MATCH('INCOME STATEMENT'!$B$73;AIRCRAFT!$D$2:$D$32;0);AIRCRAFT!$D:$D)

if you are looking to get the amount on the sheet "income statement" from the sheet "aircraft" or a different field I think you need index/match. Maybe this in cell D76 of the income statement sheet?
=INDEX(AIRCRAFT!H2:H10,MATCH($B$73,AIRCRAFT!B2:B10,0)) If you are looking to return the matched value in column D of "aircraft" then change the H2:H10 to D2:D10 like this...
=INDEX(AIRCRAFT!D2:D10,MATCH($B$73,AIRCRAFT!B2:B10,0))

I thank you for your help, the formula that have worked was =INDEX(AIRCRAFT!D2:D10;MATCH($B$73;AIRCRAFT!B2:B10;0)) but it have repeated some cells, how can I sort down all on Income Statement D76 to D87 the information herein Aircraft in Aircraft D2 to D10 if the Word in Income Statement B73 is equal with the Column B in Aircraft?

ok, this is trickier, for me I would need a couple helper columns. One I added to the aircraft sheet in column AC that counts the number of times that the value shows up. The next helper I added was to your income statement in cells C 76 down to C85. Then the index match statement in cells D76 through D85. See what you think.

This have worked, many thanks for your help on this.

Kind Regards
Fausto