Looking up a reference

markbanin

New Member
Joined
Mar 17, 2002
Messages
14
I want to look up a supplier code in column A
and have it return the Address in column C (which occupies 6 rows). When I use VLOOKUP it will only give the corresponding SINGLE row.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Mark,

In column A supplier code
In coloumn B address of supplier code (format width for the whole address
In column C enter =vlookup(d1,range,2) format width to match column B
In D1 enter supplier code
 
Upvote 0
On 2002-04-11 01:38, markbanin wrote:
Thanks Brian

But I really need to keep the address on seperate rows.

Mark
Column J supplier code
How about separate columns i.e column k street
Column l city
Column m state etc.

Then on another sheet or somehwere on the same sheet just refer to the preceding column

In B1 =vlookup(a1,range,2)
In c1 = vlookup(b1,range,3)
In d1 =vlookup(c1,range,4)

enter supplier code in A1

or PM your email, I'll send a sample spreadsheet
This message was edited by Brian from Maui on 2002-04-11 02:00
This message was edited by Brian from Maui on 2002-04-11 02:01
 
Upvote 0
On 2002-04-11 01:57, Brian from Maui wrote:
On 2002-04-11 01:38, markbanin wrote:
Thanks Brian

But I really need to keep the address on seperate rows.

Mark

How about separate columns i.e column k street
Column l city
Column m state etc.

Then on another sheet or somehwere on the same sheet just refer to the preceding column

In B1 =vlookup(a1,range,2)
In c1 = vlookup(b1,range,3)
In d1 =vlookup(c1,range,4)

enter supplier code in A1

or PM your email, I'll send a sample spreadsheet
This message was edited by Brian from Maui on 2002-04-11 02:00

Brian,

On your proposal,

B1 =vlookup(a1,range,2)
c1 = vlookup(b1,range,3)
d1 =vlookup(c1,range,4)

should be:

B1 =vlookup(a1,range,2)
c1 = vlookup(a1,range,3)
d1 =vlookup(a1,range,4)

Aladin
 
Upvote 0
On 2002-04-11 02:02, Aladin Akyurek wrote:
On 2002-04-11 01:57, Brian from Maui wrote:
On 2002-04-11 01:38, markbanin wrote:
Thanks Brian

But I really need to keep the address on seperate rows.

Mark

How about separate columns i.e column k street
Column l city
Column m state etc.

Then on another sheet or somehwere on the same sheet just refer to the preceding column

In B1 =vlookup(a1,range,2)
In c1 = vlookup(b1,range,3)
In d1 =vlookup(c1,range,4)

enter supplier code in A1

or PM your email, I'll send a sample spreadsheet
This message was edited by Brian from Maui on 2002-04-11 02:00

Brian,

On your proposal,

B1 =vlookup(a1,range,2)
c1 = vlookup(b1,range,3)
d1 =vlookup(c1,range,4)

should be:

B1 =vlookup(a1,range,2)
c1 = vlookup(a1,range,3)
d1 =vlookup(a1,range,4)

Aladin

Aladin,

I stand corrected :oops:
Must be the euphoria
Can you help apple, I bit off more than I could chew?

Brian
 
Upvote 0
On 2002-04-11 01:10, markbanin wrote:
I want to look up a supplier code in column A
and have it return the Address in column C (which occupies 6 rows). When I use VLOOKUP it will only give the corresponding SINGLE row.

Try this.

if your lookup value is in A1 in B1
use your ordinary VLOOKUP() for the first line of the address.
on the next row down use something like:

=OFFSET(INDIRECT(ADDRESS(MATCH(B1,C:C),1)),1,0)

copy down as needed

any good?

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-04-11 03:01
 
Upvote 0
Thanks Ian

I've never used any of those functions before. Here goes the F1 key to find out how they work.

regards

Mark
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top