SOLVED

Excel formula query

Brass Contributor

Hi all and thank you in advace for your help.

 

The first query I have is how to write a formula that searches column C (Channel Partner) for a match in column G, (Suppliers) when it finds a match it takes the data in Column H and inserts it in column E.

 

The 2nd query requires a formula that matches the ID number in column B with the ID Number is Column J and takes the corresponding invoice value data in Column K and puts it in column F on the same row as the matched ID number.

 

The end result I am looking for is to take 2 sets of data and merge them so I can allocate a Parner Abrev to each channel partne and then work out how much those with a partner abrev of "BB" & "AA" generate in invoice value

 

Cheers - Rob

5 Replies

@robwill100

Is this what you want to do?

@OliverScheurich Thanks for your help. 

 

Thanks for your quick response on this query.

I can see that the ID number in column B is matchimg with Column J and pulling the invoice value into column F which is what it should do.

The only thing that appears not to be quite right is that the channel partner name in colum C should have the partner ID in Column E that is returned from the list in column H which for that particular channel partner.

Therefore the formula needs to take the data in C2, "Mai Baker" and seasrch for the correseponding name in Column G and return the correspopnding partner abbrev in Colimn H to the partner ID Column

The above examples show that "Mai Baker" in C2 should have a Partner id of AA in Column E however it is showing BB.

@robwill100 Try the attached file.

best response confirmed by robwill100 (Brass Contributor)
Solution

@robwill100 

=IFERROR(VLOOKUP(C2,$G$2:$H$14,2,FALSE),"")

 

I changed the formula for column E (Partner ID) as shown above. This works in my sheet. Unfortunately i didn't understand in the beginning what you want to do. 

 

This formula changes the values in columns L and M as well. Are the results in columns L and M what you finally want to do?

Thanks for your help, formula is working well.

Cheers
1 best response

Accepted Solutions
best response confirmed by robwill100 (Brass Contributor)
Solution

@robwill100 

=IFERROR(VLOOKUP(C2,$G$2:$H$14,2,FALSE),"")

 

I changed the formula for column E (Partner ID) as shown above. This works in my sheet. Unfortunately i didn't understand in the beginning what you want to do. 

 

This formula changes the values in columns L and M as well. Are the results in columns L and M what you finally want to do?

View solution in original post