vlookup querie (2003)

[verada]

Loungers,

Is there an easy way to determine the col_index_num, other than having to count the columns, as I have a fairly wide spreadsheet with hidden columns?

[GoCush]

Is the simple solution out of the question: Insert a row at the top (new row 1) and number the cells across? This row could be hidden again and references made to these cells in order to get the number; or left visible.

[sdckapr]

In addition to Paul's suggestion, you can choose a cell in the column and use COLUMN function (you will have to adjust the number to the first column of the range). This method is advantageous if you datarange will be expanding and contracting.

Another option would be to use a combination of MATCH and INDEX. MATCH gives the row number and you can just use the INDEX based on the column of interest. This does not require an "offset" at all (this is the way if you to VLOOKUP to the left rather than the right)

Steve

[verada]

Steve & Paul

Thanks for your suggestions - I wonder if later versions may enable you to use the column name - ie AZ rather than a number?

Might make things easier.

[sdckapr]

<P ID="edit" class=small>(Edited by sdckapr on 21-Mar-06 05:50. Added PS)</P>You can use it if you use the column function...

Lookups do not use the absolute reference, it uses a type of "offset" from the lookup column.

If you want to use column references, as mentioned previously, the MATCH/INDEX combo works this way.

Steve
PS. Checkout Re: Vlookup to the left? (Excel: 97-2002) for an example. This may be more what you are after...

[verada]

Steve,

Thanks for the advise - I had a look at your thread - but not all that sure - I'm using the vlookup below, how would this be modified to use the match/index combo as you suggest?

=VLOOKUP($A$19,'Data Input Sheet'!A4:AC35,27,FALSE)

Regards

[HansV]

=INDEX('Data Input Sheet'!AA4:AA35,MATCH(A19,'Data Input Sheet'!A4:A35,0))

(column AA is the 27th column)

[sdckapr]

Hans answered your question, but 1 comment (which I think is one of the advantages to the INDEX/MATCH) is that inserting/deleting columns will keep the right column linked for the lookup.

The vlookup will not be offset correctly since the value (27 in your example) will not adjust itself.

Other advantages of index/match is that you can VLOOKUP to left, and you can also lookup on different sheets/workbooks since the 2 ranges are independent (this could be a problem is you do not keep them "synced" with the same "rows")

Steve

[jeffersdj]

Hans, Steve

Thanks for your assistance