Vlookup

Pjesani

New Member
Joined
Dec 13, 2004
Messages
45
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

Pjesani

New Member
Joined
Dec 13, 2004
Messages
45
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





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
Joined
May 15, 2003
Messages
8,638
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
Joined
Dec 13, 2004
Messages
45
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.
 

Forum statistics

Threads
1,148,055
Messages
5,744,544
Members
423,882
Latest member
Seeham

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top