I have an excel sheet with a cheque listing of several 100 lines. The gross column is "F" and there are some 50 headings for analysis. Luckily there is no VAT so gross = net. I am trying to create an excel formula to find the appropriate analysis figure that matches the gross and then return the column heading. Should be simple be i must be doing something wrong as i keepgetting an error message. Any ideas.
Ian Stacey
Replies (4)
Please login or register to join the discussion.
There's lots of free Pivot Table material on AW.co.uk
Bob - just to put the record straight, there is also a ton of free pivot table material on AW.co.uk, written by David Carter (it's so good that he should be charging for it!). Check out the article:
Interested in Pivot Tables? Start here
If you complete your guide to pivot tables and it doesn't overlap too closely with David's guides, we'd be interested in posting it in our Excel area.
John Stokdyk
Editor
AccountingWEB.co.uk
Using Ian's MATCH() and Bob's Ideas
Based on Bob's spreadsheet the "More Elegant" formulae might be:-
=INDEX(H$1:J$1,,MATCH(F2,H2:J2))
Where INDEX(ARRAY,,COLUMN_Num) Returns the item in the ARRAY at that position
Don