Trying to use if and offset together.....

[sedwardson]

Hiya,

I have a workbook with two worksheets in. Worksheet one has a list of telephone numbers starting in column A2 -> A143 and in D2 -> D143 are their respective owners. The second worksheet has a list of telephone numbers with charges associated with them. I want the second worksheet to lookup the numbers in the first worksheet and put the associated owners name into column C starting at Row 2. I've looked at using the standard offset but this wont look through my range of numbers to find the corresponding owner and also tried if, countif and match statements all to no avail. I suspect either my syntax has been incorrect each time or I'm using the wrong function. Hopefully the attached will help explain further. Thanks in advance for all and any help.

Phone	Short Code	Full Name	Company
01234 567890		Bob	Company 1
01234 567891	201	Frank	Company 2
01234 567892	202	Harry	Company 3
01234 567893	203	Jerry	Company 4
01234 567894	204	Sue	Company 5

Number	Total
01234 567890	£6.00
01234 567891	£6.00
01234 567892	£0.00
01234 567893	£6.00
01234 567894	£6.00

 

[awoohaw]

are the numbers text or numbers? They look like text in your mini worksheets you have posted because they are left aligned.

 

[awoohaw]

seems to work okay for me:

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I
1	Phone	Short Code	Full Name	Company			Number	Total	company
2	01234 567890		Bob	Company 1			01234 567890	£ 6.00	Company 1
3	01234 567891	201	Frank	Company 2			01234 567891	£ 6.00	Company 2
4	01234 567892	202	Harry	Company 3			01234 567892	£ -	Company 3
5	01234 567893	203	Jerry	Company 4			01234 567893	£ 6.00	Company 4
6	01234 567894	204	Sue	Company 5			01234 567894	£ 6.00	Company 5
7
sedwardson

Cell Formulas
Range	Formula
I2:I6	I2	=VLOOKUP($G2,$A$2:$D$6,4,0)

with lookup values sorted differently:

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I
1	Phone	Short Code	Full Name	Company			Number	Total	company
2	01234 567890		Bob	Company 1			01234 567894	£ 6.00	Company 5
3	01234 567891	201	Frank	Company 2			01234 567893	£ 6.00	Company 4
4	01234 567892	202	Harry	Company 3			01234 567892	£ -	Company 3
5	01234 567893	203	Jerry	Company 4			01234 567891	£ 6.00	Company 2
6	01234 567894	204	Sue	Company 5			01234 567890	£ 6.00	Company 1
sedwardson

Cell Formulas
Range	Formula
I2:I6	I2	=VLOOKUP($G2,$A$2:$D$6,4,0)

 

[sedwardson]

Thanks @awoohaw ! That did the job perfectly ! I was even able to expand on it slightly to make the sheet even better. Again, thanks for your help.

 

[awoohaw]

My pleasure, i'm happy you were able to find a solution in the forum.

Best wishes!