Need help with VLookup Formula :(

[Varion]

Hi all

Im trying to create a worksheet which will have an invoice sheet to the left of the screen and a table for suppliers addresses to the right numbered 1-30. Within the invoice sheet I have tried to create a formula within a single cell,which when you put a number from 1-30 in it will drop the corresponding address from the table to the right into a larger cell within the invoice part of the worksheet.

ive posted this request before and was told to use =Vlookup(1,B2:B31,True) which I did put it only pulls across a single address from the table. I need the formula to enable toput whatever number in between 1-30 and drag the the equivalent numbered address across.


Thx in advance

 

[NBVC]

As mentioned in our initial thread... http://www.mrexcel.com/board2/viewtopic.php?t=234913&highlight=

it would be easier for us to visualize what your asking if you posted a sample using Colo's Html Maker....

 

[barry houdini]

If your table is G2:H31 with numbers 1 to 30 in G2:G31 and corresponding addresses in H2:H31 then try this formula in B1

=VLOOKUP(A1,$G$2:$H$31,2,0)

where A1 contains the number (1-30) for which you want to return an address

 

[Varion]

Book1
	B	C	D	E	F	G	H
3
4						Code	Supliers
5						1	bil
6						2	dave
7						3	alan
8						4	etc
9		Addresscell				5	etc
10						6	etc
11						7	etc
12	Inputcell					8	etc
13						9	etc
14						10	etc
15						11	etc
16						12	etc
17						13	etc
18						14	etc
19						15	etc
20						16	etc
21						17	etc
22						18	etc
23						19	etc
24						20	etc
25						21	etc
26						22	etc
27						23	etc
28						24	etc
29						25	etc
30
31
32
Sheet1

 

[Varion]

oopps a bit big I think

 

[NBVC]

To me it seems that you want the Vlookup() formula as I had suggested in your other thread

=Vlookup(number,table,column index #,False)

in this sample it would be =Vlookup(B12,G5:H29,2,False)

So that should work unless I am still not understanding your request.

 

[Varion]

Okay guys ill try again with what your saying back again tomoz with a reply thanx for your time again

 

[Varion]

One last question if i do these formulas will it drop the address into the cell C9 or will it be B12 where I originally typed the number ?

 

[NBVC]

One last question if i do these formulas will it drop the address into the cell C9 or will it be B12 where I originally typed the number ?

It will drop the address wherever you place the formula (as long as you don't place it in B12 or within your lookup table).