Lookup by Last Column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nash13 - 23 Apr 2007 23:36 GMT

I have a lookup that doesn't work unless I move the last column of the sheet
being looked up to the first column. How can I get it to look by the last
column? Unfortunately it is another workbook and can't be moved. I have my
current example below. Thanks

=VLOOKUP($A$4,[SalesNumbersConsumer4.20.07.xls]WkQA!$A$1:$R$1000,4,FALSE)

Reply to this Message

Dave Peterson - 23 Apr 2007 23:45 GMT

You could use =index(match())

Kind of like this within the same workbook:
=index(sheet2!a:a,match(a1,sheet2!r:r,0))

Debra Dalgleish shares more info:
http://www.contextures.com/xlFunctions03.html

> I have a lookup that doesn't work unless I move the last column of the sheet
> being looked up to the first column. How can I get it to look by the last
> column? Unfortunately it is another workbook and can't be moved. I have my
> current example below. Thanks
>
> =VLOOKUP($A$4,[SalesNumbersConsumer4.20.07.xls]WkQA!$A$1:$R$1000,4,FALSE)

Signature

Dave Peterson

Reply to this Message

Nash13 - 24 Apr 2007 00:12 GMT

Dave, I copied each of the examples from that web page into Excel and none
seem to work. Am I missing something?

> You could use =index(match())
>
[quoted text clipped - 10 lines]
> >
> > =VLOOKUP($A$4,[SalesNumbersConsumer4.20.07.xls]WkQA!$A$1:$R$1000,4,FALSE)

Reply to this Message

Don Guillett - 23 Apr 2007 23:51 GMT

You might try a defined name referring to the range in the other workbook.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I have a lookup that doesn't work unless I move the last column of the
>sheet
[quoted text clipped - 3 lines]
>
> =VLOOKUP($A$4,[SalesNumbersConsumer4.20.07.xls]WkQA!$A$1:$R$1000,4,FALSE)

Reply to this Message

Nash13 - 24 Apr 2007 00:12 GMT

If I define the range, how do I look up by it? Thanks

> You might try a defined name referring to the range in the other workbook.
>
[quoted text clipped - 5 lines]
> >
> > =VLOOKUP($A$4,[SalesNumbersConsumer4.20.07.xls]WkQA!$A$1:$R$1000,4,FALSE)

Reply to this Message

Nash13 - 24 Apr 2007 00:16 GMT

Okay, I got it. Thanks guys.

> You might try a defined name referring to the range in the other workbook.
>
[quoted text clipped - 5 lines]
> >
> > =VLOOKUP($A$4,[SalesNumbersConsumer4.20.07.xls]WkQA!$A$1:$R$1000,4,FALSE)

Reply to this Message

Don Guillett - 24 Apr 2007 00:54 GMT

The archives would like to know what you used.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Okay, I got it. Thanks guys.
>
[quoted text clipped - 10 lines]
>> >
>> > =VLOOKUP($A$4,[SalesNumbersConsumer4.20.07.xls]WkQA!$A$1:$R$1000,4,FALSE)

Reply to this Message