Advanced lookup/Reference Excel 2010

[harry123]

Hi all,

I'm using Excel 2010 and Windows 7, and have exhausted all Help avenues.

My spreadsheet looks like this:
Two sheets, one is F9 and the other is Lookup

F9 (Line number is NOT the Excel row number)
Line Nr|Line Desc|Amt|Allocations
1 |overhead |100|ovh
165 |Misc |150|admin |aud |good

Lookup
Allocation | Line Nr | Desc
Ovh | To be looked up from F9
admin |
aud |
good |

I have tried a huge nested IF statement, with several MATCH and INDEX statements, but to no avail, as there can be up to 8 columns of allocations. Due to 8 columns in use (only the first 5 could be supported), I had to use a second column to continue the nested IF, but everything falls apart when the allocation is in the second column.

What do I want to do?
The lookup sheet has to be able to pull in the relevant line nr and description, when the allocation is given. In Lookup sheet, the first row (Ovh) needs to pick up Line nr 1, while admin, aud and good needs to pick up line nr 165.

Any ideas?

 

[Andrew Poulsom]

If the allocation can only appear once:

=INDEX('F9'!A$1:A$3,SUMPRODUCT(('F9'!$D$1:$F$3=$A2)*ROW('F9'!$A$1:$A$3)))

in B2 copied down and across.

 

[harry123]

Hi Andrew,

I've done a quick test, and it works like a charm!

Thanks!!