Lookups Excel

[daveinuk]

I have a really hard one here that im sure you lot will find really easy!

I have the following columns:

Sheet 1
column F - Unit No
column G - Street


Sheet 2
Column A - Street
Column B - Unit No
Column C - Unit ID


I need to copy the unit ID's from sheet 2 onto the same row as the street and unit no on sheet 1's column B.

Any ideas on how to do this?

Many thanks.

David.

 

[AnotherHiggins]

You haven't provided very much information. Do the same Unit Numbers appear on different sheets? If not, then use something like this:

Sheet 1
column H - [COLOR=blue white]=vlookup(F2,Sheet2!B:C,2,0)[/color]

If that doesn't work for you, please provide some sample data that represents the different scenarios you will run into.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[daveinuk]

I knew I would not provide a lot of info!

right here is a sample of sheets:

Sheet 1

Unit Road
No. Name

001 AUSTIN COTE LN
002 AUSTIN COTE LN
003 AUSTIN COTE LN
004 AUSTIN COTE LN
005 AUSTIN COTE LN
006 AUSTIN COTE LN
007 AUSTIN COTE LN
001 BLACKTHORNE RD FP LDC
002 BLACKTHORNE RD FP LDC
003 BLACKTHORNE RD FP LDC



and here is sheet 2

STREET NAME UNIT NO UNIT ID

UPPER ST JOHN ST 000 208748
SCHOOL LN EDINGALE 001 210526
SCHOFIELD LN EDINGALE 001 210527
WISSAGE RD 001 212928
DEANSLADE DR 001 213153

they are 2 sheets in the same workbook. I need to put the unit ID from sheet 2 next to the unit no and road name in sheet 1. Does this make sense? Thanks. David.

 

[AnotherHiggins]

Yes, much more clear now. Try this:

[COLOR=blue white]=SUMPRODUCT((Sheet2!B2:B1000=F2)*(Sheet2!A2:A1000=G2)*(Sheet2!C2:C1000))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.