Vlookup

[Pjesani]

Urgent help please!


I want to do a look up for one spreadsheet that looks like

Property Ref ITOCC Total
1 A4 10
B2 5
C10 12
C11 26
C13 500
C14 4,860

2 A4 67,032
B2 25,500
C10 39
C11 34
C13 1,000
C14 14
C15 84
C15 29

7 A1 2,000
A4 468
B2 75
C10 888
C11 6,00
C13 160
C14 117

The spreadsheet i want the totals populated in looks like

A1 A2 A3 A4 A5 A6 etc
1
2
3
4
5
6

They 1,2,3,4 are reference numbers that match with the above spreadsheet .

How do i match the reference in this spreadsheet with the above and pick the total specified in the right code catergory to populated in this spreadsheet.

Thanks

 

[fairwinds]

Hi,

Try:

D2: =IF(A2,A2&"@"&B2,SUBSTITUTE(D1,"@"&B1,"@"&B2))
drag down

E3: =IF(ISNUMBER(MATCH($F3&"@"&G$2,$D$2:$D$24,0)),INDEX($C$2:$C$24,MATCH($F3&"@"&G$2,$D$2:$D$24,0)),"")
drag right / down

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Property	ITOCC	Total
2	1	A4	10	1@A4			A1	A2	A3	A4	A5
3		B2	5	1@B2		1				10
4		C10	12	1@C10		2				67032
5		C11	26	1@C11		3
6		C13	500	1@C13		4
7		C14	4860	1@C14		5
8				1@		6
9	2	A4	67032	2@A4
10		B2	25500	2@B2
11		C10	39	2@C10
12		C11	34	2@C11
13		C13	1000	2@C13
14		C14	14	2@C14
15		C15	84	2@C15
16		C15	29	2@C15
Sheet2

 

[Pjesani]

Hi, Thanks for your reply

Tried the formula below for second formula i put in this according to my cells and my lookup is in another spreadsheet

=IF(ISNUMBER(MATCH($A8&"@"&B$2,Sheet1!$D$2:$D$470,0)),INDEX(Sheet1!$C$2:$C$470,MATCH($A8&"@"&B$2,Sheet1!$D$2:$D$470,0)),"")

But it does not give me anything for any cells.

Regards

Premila

Hi,

Try:

D2: =IF(A2,A2&"@"&B2,SUBSTITUTE(D1,"@"&B1,"@"&B2))
drag down

E3: =IF(ISNUMBER(MATCH($F3&"@"&G$2,$D$2:$D$24,0)),INDEX($C$2:$C$24,MATCH($F3&"@"&G$2,$D$2:$D$24,0)),"")
drag right / down

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Property	ITOCC	Total
2	1	A4	10	1@A4			A1	A2	A3	A4	A5
3		B2	5	1@B2		1				10
4		C10	12	1@C10		2				67032
5		C11	26	1@C11		3
6		C13	500	1@C13		4
7		C14	4860	1@C14		5
8				1@		6
9	2	A4	67032	2@A4
10		B2	25500	2@B2
11		C10	39	2@C10
12		C11	34	2@C11
13		C13	1000	2@C13
14		C14	14	2@C14
15		C15	84	2@C15
16		C15	29	2@C15
Sheet2

 

[fairwinds]

As I do not know your actual ranges, I cannot say if you got that right but as far as I can tell, the formula looks OK. I guess you need to check your data and ensure that you have exact matche, no empty spaces etc.

 

[Pjesani]

Yes that formula works!! When out for lunch and came back and i spotted my mistake with the cell range. It has saved me an a great deal of time.

Many thanks


Premila

As I do not know your actual ranges, I cannot say if you got that right but as far as I can tell, the formula looks OK. I guess you need to check your data and ensure that you have exact matche, no empty spaces etc.