# Vlookup

#### Pjesani

##### New Member

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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Pjesani

##### New Member

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

fairwinds said:
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
ABCDEFGHIJK
1PropertyITOCCTotal
21A4101@A4A1A2A3A4A5
3B251@B21   10
4C10121@C102   67032
5C11261@C113
6C135001@C134
7C1448601@C145
81@6
92A4670322@A4
10B2255002@B2
11C10392@C10
12C11342@C11
13C1310002@C13
14C14142@C14
15C15842@C15
16C15292@C15
Sheet2

#### fairwinds

##### MrExcel MVP
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

##### New Member
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

fairwinds said:
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.

Replies
13
Views
582
Replies
3
Views
310
Replies
9
Views
182
Replies
7
Views
465
Replies
5
Views
217

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,821
Messages
5,766,639
Members
425,364
Latest member
jstubs99

### 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.

### Which adblocker are you using?

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

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