VLOOKUP

Topic

New Reply

I am using VLOOKUP to find school subject names from their codes. The workbook in which I do the lookup has several named ranges, when I go to this book via the Window menu then go to the Insert menu / Names, sometimes the Paste option is available but mostly it is grayed out. Why is this? It was not a problem in XL95 where the name box was available when you went to the lookup book, but Microsoft in their wisdom eliminated this in XL97

Reply | Quote

Replies

Can you attach a censored example?

Reply | Quote

SubjectCodes.xls contains the subject names and codes, and the range with all the data is named ‘Codes’.

Trial.xls contains a few codes.

Open both files.

If in Trial.xls, cell A2, you type “=VLOOKUP(A1,” (without the “s), then go to the Window Menu and choose SubjectCodes.xls, then Insert/Names, you will find all the options grayed out. However on occasion I have found the Paste option available, and on choosing this I get a list of Range Names which I can choose from, in the present case I would choose ‘Codes’. This is very convenient (and in XL95 you could do this everytime because the Name Box was available), but I cannot work out why in XL97 sometimes it is available and sometimes not.

Reply | Quote

Here is SubjectCodes.xls. Is it possible to attach 2 files to one post?

Reply | Quote

When you “=VLOOKUP(A1,”, and then goto the other book, try pressing F5 (or Ctrl-G) and you should get a list of named ranges, and you click on the appropriate choice. I know it is not the answer to your question, but I cannot explain the behaviour, which I have encountered myself from time to time.

Andrew C

Reply | Quote

Wonderful! Thank you Andrew. That has frustrated me ever since I moved to XL97.

Reply | Quote

A really quick way is to press F3 to paste in the named range. I haven’t ever had the greyed out stuff when using this method. As an aside, you have included the headings Code and Name in the named range (in your attachment) and this can produce incorrect results because the lookup column (A) has to be sorted to work properly. For instance I got #N/A in B1 when it looked up A1, but from B2 onwards it worked fine.

Reply | Quote

Suzanna. Thanks for the tip about F3; F3 or F5 seem to work equally well in this case. If you set range_lookup to false (as I always do) the table_array does not have to be sorted. I have never had any problems caused by including the column headings in the table_array.

Reply | Quote

That sounds thrilling Michael but I’m not sure I understand exactly how you would do that (set range_lookup to false). I do lots of lookup tables and also have spreadsheets where extra info gets entered into the lookup range by other people which then goes haywire if the person forgets to sort it. Could you give an example please?

Thanks in advance.

Reply | Quote

Suzanna:
The “False” statement is for VLOOKUP and HLOOKUP. It is the last term in the list of arguments for the functions. As far as your problems with “where extra info gets entered into the lookup range by other people”, if you define your named range using =offset($col$Row,0,0,counta($X:$X),N) (in the REFERS TO: window of the Dialog box), where $col$Row is the upper left corner of your data table, N is the width of your table, and X is a column within your data table with no entries above the column header and none below the last row of data. If $col$Row is a header for the first column of data, all your data will always be included with headers no matter how many rows others may add. You could then, for example, use paste that named range as a Pivot Table (which requires the headers). If you do not want the headers drop down one line and use the following for the Named Range: =offset($col$Row,0,0,counta($X:$X),N).
I have probably given you more information than you wanted but I have had a few questions about how to do this and thought it would be a good opportunity to put it out there.
Stephen

Reply | Quote

Thank you very much for that info

Reply | Quote

Hi Charlene,

I tried to replicate your data structures, and can see what you mean. The MATCH function works fine when specific text is being searched, but there does seem to be a problem with non integer numbers. I think it may have something to do with sorting but without actual sample data cannot really be sure. What sort of numbers are typically in D2 and P2, and what numbers are in Z9 to AH9 ?

If you could post a sample sheet with dummy data I could see if I can locate the problem.

Andrew

Reply | Quote

Charlene,

Your attachment did not happen, Could you re-post.

Reply | Quote

Charlene,

Insofar as you have a problem, it is with VLOOKUP rather that match. When you pass VLOOKUP an offset parameter, you really need to add 1 to it as passing 1 returns the actual value you are looking up. So the following slight amendment to your formula should fix it :

=VLOOKUP(S8,T11:AB15,MATCH(($P$2/$D$2),$U$10:$AB$10)+1)

Andrew

Reply | Quote

Charlene:
I really have the feeling that I should not have read this series of Posts. But I did. So, take a look at my revision of your Wkbk; it has the following changes:
1) I reversed rows T9;ab9 and T10:AB10;
2) I entered an alternate formula in cell B5 that seems to do what you want.
I did not test for all conditions due to time constraints and my ignorance of what your application is all about. I believe that MATCH function is a bit cranky and you can never really trust what EXCEL will come up with as a result of a math operation (e.g., try the following eq =IF(99-98.02=0.98,”yes”,”No”). This explains why I use “INT” function even though my condition says that the quotient is a whole number.
You may want to do some testing and modify as needed. You may also need to add an if statement to prevent the column in VLOOKUP from refering to cell AC10.
Good luck. Hope I did not confuse things more.
Stephen

Reply | Quote