Vlookup with a twist

Posted by Clive le Roux on November 01, 2001 12:55 PM

I have 2 worksheets. Worksheet 1 has rows with names and the number of columns used for each row varies. e.g

name1 col1 col2 col3 col4
name2 col1 col2 col3
name3 col1
name4 col1 col2 col3 col4 col5 col6

in worksheet 2 I want to lookup the last used column on worksheet 1 for each name using the vlookup formula, however with vlookup you need to specify the column.
The following formula works, but how can I can I replace the "Sheet1!$1:$1" with a row variable instead of being fixed?
=VLOOKUP("name1",archiving,MATCH(0, Sheet1!$1:$1, -1),TRUE)

Any ideas?
Thanks
Clive

Posted by Aladin Akyurek on November 01, 2001 1:26 PM

Try the following longish formula instead:

=INDIRECT(ADDRESS(MATCH(lookup-value,Sheet1!A1:A10,0),MATCH(9.99999999999999E+307,INDIRECT(MATCH(lookup-value,Sheet1!A1:A10,0)&":"&MATCH(lookup-value,Sheet1!A1:A10,0)))))

You can also simplify this formula in a cell in the worksheet from you which you do looking up:

In say E1 enter: =MATCH(lookup-value,Sheet1!A1:A10,0)

The above formula becomes then:

=INDIRECT(ADDRESS(E1,MATCH(9.99999999999999E+307,INDIRECT(E1&":"&E1))))

Caveat. The above formulation requires that Sheet1 does not contain any other data but the data of interest.

Aladin

==========

Posted by Mark W. on November 01, 2001 1:39 PM

How do you know that the non-key columns contained only numbers? (nt)

Posted by Aladin Akyurek on November 01, 2001 1:43 PM

That's the second caveat that must be noted. (NT)

Posted by Clive le Roux on November 01, 2001 2:01 PM

The column values are all dates.

e.g.
name1 12/02/01 21/03/01 14/06/01
name2 11/01/00 15/03/00 31/12/01
etc
etc

Posted by Aladin Akyurek on November 01, 2001 2:13 PM

Re: The column values are all dates.

Clive --

That's just fine. Date format the cell where you put the formula that I suggested.

Aladin