Lookup in Excel

Lookup in Excel

Didn't find your answer?

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.

John Stokdyk, AccountingWEB head of insight
By John Stokdyk
12th Sep 2002 15:47

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

Thanks (0)
avatar
By User deleted
06th Sep 2002 23:45

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

Thanks (0)
avatar
By AnonymousUser
05th Sep 2002 17:11

Success
HLOOKUP(+MATCH(F5,G5:AU5,1),H$2:AU$3,2,FALSE)

What i came up with in the end was to insert another row which incremented from 1 -> the last column for the analysis. The above then worked in that the formula looks from the gross column and finds the number for its position in the array, compares this to the numbers inserted at the top and then returns the title of the column. I can now get a vertical list of the column headings against each line.

Might seem a bit cack handed but i do now get the end result.

Thanks (0)
avatar
By AnonymousUser
05th Sep 2002 17:24

Lookup formula
As Bob suggested, the first column (If vertical data list) first row (If horizontal data list)should be in ascending order and the looking up value should be unique value or ID. For example based on cheque number you can bring that cheque related information some where else. I use this formula to do massive reconciliation.(about 10000 rows and 50 columns at a time). Also if add at the end of formula "False" (=vlookup(A2,A50:D50,2,"False")
Here I am looking for unique value I enter in A2 from the range A50 to D50 and bringing information from column "B", by entereing "False", then it gives me flexibility of not to have data in ascending order.

If you like you can email me your spread sheet with your requirement then I will have a look at it and I may find the solution. [email protected].

I hope this helps.


Thanks (0)