Match cells in 2 columns and return third column value if equal

[beaglesBuddy]

I have 2 sheets with thousands of clients. One has client contact information and one revenue. I was able to put the 4 columns on 1 sheet. I need a formula that will say if the value of the client name columns match, (Client name) then return the value in the third column (revenue).

One other question, b/c i copied from a pivot table, i have the client name and their email address below in a separate row. Ideally i would have the client their contact and their revenue in 3 columns next to each other. Is there an easy way to bulk move the email address from the row below the column to the column next to the client name?

Thanks!

[Pete_UK]

Put the following formulae in the cells stated:

L1: =IF(INDEX(A:A,(ROWS($1:1)-1)*3+1)="","",INDEX(A:A,(ROWS($1:1)-1)*3+1))

M1: =IF(L1="","",INDEX(A:A,(ROWS($1:1)-1)*3+2))

N1: =IFERROR(VLOOKUP(L1,$G$1:$I$8,3,0),"")

then copy the formulae down as far as you need to (until you get blanks).

Hope this helps.

Pete

[etaf]

if the information is formated as the example
then in B1 put

=IF(OR(A2="",ISERROR(FIND("@",A2,1))),"",A2)

that will copy up the email address next to the client
you can then always add another code and sort - using the find() to then insert a code to filter or sort and remove the email rows

then you can use vlookup or index/match to get the revenue

=IFERROR(VLOOKUP(A1,$G$1:$J$9,3,FALSE),"")

in fact you could use the iferror

so
=IFERROR(VLOOKUP(A1,$G$1:$J$9,3,FALSE),"ZZZZZZ")

now sort - all the ZZZZ will be together and then you can delete

[beaglesBuddy]

Thanks this worked very well thanks. Im kind of new though how do i use find() to remove the old email addresses for A column?

if the information is formated as the example
then in B1 put

=IF(OR(A2="",ISERROR(FIND("@",A2,1))),"",A2)

that will copy up the email address next to the client
you can then always add another code and sort - using the find() to then insert a code to filter or sort and remove the email rows

then you can use vlookup or index/match to get the revenue

=IFERROR(VLOOKUP(A1,$G$1:$J$9,3,FALSE),"")

in fact you could use the iferror

so
=IFERROR(VLOOKUP(A1,$G$1:$J$9,3,FALSE),"ZZZZZZ")

now sort - all the ZZZZ will be together and then you can delete

[etaf]

i would use the iferror section and then sort or filter on that revenue column

=IFERROR(VLOOKUP(A1,$G$1:$J$9,3,FALSE),"ZZZZZZ")

or use
Pete_UK solution

the i would select columns L,M,N
and copy
paste special > value

now you can delete the other columns , and have a clean set of columns

[beaglesBuddy]

Thing is not every client will have revenue. Some didnt do anything so they would show up as a blank but i still need them listed on the sheet with their email address to the right.

i would use the iferror section and then sort or filter on that revenue column

=IFERROR(VLOOKUP(A1,$G$1:$J$9,3,FALSE),"ZZZZZZ")

or use
Pete_UK solution

the i would select columns L,M,N
and copy
paste special > value

now you can delete the other columns , and have a clean set of columns

[beaglesBuddy]

Nevermind. I filtered out anything that contained @ and deleted. Thanks for everyones help!

[etaf]

does that mean the client will not be listed in the table or in the table but with zero as the revenue ?