Hi guys,
i believe i need to use a vlookup formula for this but not sure 100% hopefully someone can help
basically as you can see B5 & B6 searches the pc coverage sheet if the prefix appears in it and will return an agent name and telephone number in b5 & b6.
but if you can see in the pc coverage tab there is more than one agent so i need it to bring up both agents on the sheet and both the agents tel numbers.
Please help
here is one way
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
You could try a dynamic list method. In Sheet1 cell B5 paste this and then Shift+enter:
=IFERROR(INDEX('PC coverage'!$B$1:$B$42,IF($B$4="Packs",SMALL(IF($B$3='PC coverage'!$A$1:$A$42,ROW('PC coverage'!$A$1:$A$42),""),COLUMN()-1),"")),"")
in cell B6, similar thing with shift + enter:
=IFERROR(INDEX('PC coverage'!$C$1:$C$42,IF($B$4="Packs",SMALL(IF($B$3='PC coverage'!$A$1:$A$42,ROW('PC coverage'!$A$1:$A$42),""),COLUMN()-1),"")),"")
Good Luck!
If I helped you today please rate me
can you do an example of this in what ive done/attached?Originally Posted by lmvk99
how do i get that list method to work :S confused lol
it just comes up blank
also bare in mind sometimes it got have 5 different people? :S
Hi, sorry for some reason I can't seem to upload the fileI built these formulas in the sheet you provided but I didn't realise you are working in 2003. Replace your formulas with these ones and don't forget to hit Shift+Enter
B5:
=IF(ISERROR(INDEX('PC coverage'!$B$1:$B$42,IF($B$4="Packs",IF(ISERROR(SMALL(IF($B$3='PC coverage'!$A$1:$A$42,ROW('PC coverage'!$A$1:$A$42),""),COLUMN()-1)),"",SMALL(IF($B$3='PC coverage'!$A$1:$A$42,ROW('PC coverage'!$A$1:$A$42),""),COLUMN()-1)),""))),"",INDEX('PC coverage'!$B$1:$B$42,IF($B$4="Packs",IF(ISERROR(SMALL(IF($B$3='PC coverage'!$A$1:$A$42,ROW('PC coverage'!$A$1:$A$42),""),COLUMN()-1)),"",SMALL(IF($B$3='PC coverage'!$A$1:$A$42,ROW('PC coverage'!$A$1:$A$42),""),COLUMN()-1)),"")))
B6:
=IF(ISERROR(INDEX('PC coverage'!$C$1:$C$42,IF($B$4="Packs",SMALL(IF($B$3='PC coverage'!$A$1:$A$42,ROW('PC coverage'!$A$1:$A$42),""),COLUMN()-1),""))),"",INDEX('PC coverage'!$C$1:$C$42,IF($B$4="Packs",SMALL(IF($B$3='PC coverage'!$A$1:$A$42,ROW('PC coverage'!$A$1:$A$42),""),COLUMN()-1),"")))
Then you can just drag them across your columns and it will keep adding names and seriel numbers until there aren't any more.
stop using the array and use what i gave you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks