simple look up help pls

[lola0900]

Hi,
I have a range of data in sheet 1 , with number name and address.
In sheet 2 I have number and i need to populate the addresses from sheet one in there. can someone pls help with a simple formula

sheet 1

2001 Pittodrie Express 472 Kings Street Aberdeen Grampian AB24 1SA
2002 Abergele Market Street Abergele Gwynedd LL22 7AA
2004 Addiscombe Express 100-102 Lower Addisco Croyden Surrey CR0 6AD
2005 Abington Express 348 Wellingborough Ro Northampton Northamptonshire NN1 4EX
2006 Abertillery Castle Street Abertillery Gwent NP13 1UR
2007 Aberdeen Extra Laurel Drive Danestone Aberdeen Grampian AB22 8HB
2008 Abingdon Extra Marcham Road Abingdon Oxfordshire OX14 1TU
2011 Abergavenny Metro 26 Frogmore Street Abergavenny Gwent NP7 5AH

so in Sheet 2.
i want any corresponding number to automatically populate the address found in the row in sheet 1.
Is this even possible? Pls help. Many thanks

[Pepe Le Mokko]

Hi and welcome

have a look at the VLOOKUP formula for example ( other options exist)

[lola0900]

Hi,
Thanks, I ve tried the vlook up but its only returning the first cell. I need it to return the row. so if SHEET 1 CELL A1 EQUAL SHEET2 CELL A1, I want it to return SHEET 1 CELL B1;B7. Hope it makes sense. Thanks

[Pepe Le Mokko]

Supposing your data in sheet 1 is in A1:J8, in sheet 2, B1, enter

Please Login or Register  to view this content.

and drag right as far as needed. (A1 contains the lookup value)
No array formula needed here

[Jonmo1]

Clever how that works Pepe.
But it's dependant upon where the formula is entered.
If it's entered in column B, it returns the value from B in sheet1.
If it's entered in G, it returns value from G.
But if it's entered outside the A1:J8 range then it fails and returns #Value!

I would do it like this, so the index range incriments by 1 column as it's dragged right.
No longer dependant on 'where' the formula is entered.

=INDEX(Sheet1!A$1:A$8,MATCH($A1,Sheet1!$A$1:$A$8,0))

You can put the formula anywhere, and it returns value from column A on sheet1.
Then drag it right, the Index range incriments to B1:B8, then C1:C8 etc..

[Pepe Le Mokko]

Well in fact I made a mistake, not having XL at hand.n fact it IS an array formula to be entered with CSE as usual. Thx for drawing my attention. It then should be independent of the cell where entered