Index and Match

[Lizzi]

Hi,
Can someone please help me with the index/match formula, i have never used it before and i'm having issues with Vlookup as its only looking at the first value.

my sheet1 has a column (DG in my workbook) with id's in it, another worksheet (2) in the same file has a table A:C containing company names in Col A, id's in Col B and Sales persons name in Col C, i want it to match the site id in Col DG on sheet1 to the sales person in sheet2 but i don't know how to do that. some company names are the same but have different id's but the same person which is why vlookup didn't work for me.

any help greatly appreciated....thanku in advance

Liz

 

[awoohaw]

try something like this:
(I made up my own numbers)

Book1
	A	B	C	D	E	F	G
1
2	Comp Name	ID	Sales		Lkup Rng	Col DG ID	Sales
3	Comp A	402457	Liz			953004	Ben
4	Comp A	668448	Liz			899372	Paul
5	Comp A	848952	Liz			785713	0
6	Comp A	362857	Liz			402457	Liz
7	Comp B	449683	Harry			668448	Liz
8	Comp C	953004	Ben			848952	Liz
9	Comp D	899372	Paul			362857	Liz
10	Comp G	785713				449683	Harry
Sheet2

Cell Formulas
Range	Formula
G3:G10	G3	=INDEX($C$3:$C$10,MATCH(F3,$B$3:$B$10,0))

 

[Lizzi]

Thanku, it mostly worked but i got some #NA lines? even though the id matches and there is a sales person assigned to the id....confused

 

[Phuoc]

Or try this:

Book1
	A	B	C	D	E	F	G
1
2	Comp Name	ID	Sales			Col DG ID	Sales
3	Comp A	402457	Liz			953004	Ben
4	Comp A	668448	Liz			899372	Paul
5	Comp A	848952	Liz			785713	0
6	Comp A	362857	Liz			402457	Liz
7	Comp B	449683	Harry			668448	Liz
8	Comp C	953004	Ben			848952	Liz
9	Comp D	899372	Paul			362857	Liz
10	Comp G	785713				449683	Harry
Sheet1

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(VLOOKUP(""&F3,$B$3:$C$10,2,0),VLOOKUP(0+F3,$B$3:$C$10,2,0))

 

[Lizzi]

Or try this:

Book1
	A	B	C	D	E	F	G
1
2	Comp Name	ID	Sales			Col DG ID	Sales
3	Comp A	402457	Liz			953004	Ben
4	Comp A	668448	Liz			899372	Paul
5	Comp A	848952	Liz			785713	0
6	Comp A	362857	Liz			402457	Liz
7	Comp B	449683	Harry			668448	Liz
8	Comp C	953004	Ben			848952	Liz
9	Comp D	899372	Paul			362857	Liz
10	Comp G	785713				449683	Harry
Sheet1

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(VLOOKUP(""&F3,$B$3:$C$10,2,0),VLOOKUP(0+F3,$B$3:$C$10,2,0))

thank you! the first one worked actually when i realised that i had spaces in the cell, i corrected that and it worked

 

[Lizzi]

Or try this:

Book1
	A	B	C	D	E	F	G
1
2	Comp Name	ID	Sales			Col DG ID	Sales
3	Comp A	402457	Liz			953004	Ben
4	Comp A	668448	Liz			899372	Paul
5	Comp A	848952	Liz			785713	0
6	Comp A	362857	Liz			402457	Liz
7	Comp B	449683	Harry			668448	Liz
8	Comp C	953004	Ben			848952	Liz
9	Comp D	899372	Paul			362857	Liz
10	Comp G	785713				449683	Harry
Sheet1

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(VLOOKUP(""&F3,$B$3:$C$10,2,0),VLOOKUP(0+F3,$B$3:$C$10,2,0))

it worked after i use the trim formula to clean the cell up as i had trailing spaces......thanku