SOLVED

# Excel formula query

Occasional Contributor

# Excel formula query

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

# Re: Excel formula query

@robwill100

Is this what you want to do?

# Re: Excel formula query

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.

# Re: Excel formula query

@robwill100 Try the attached file.

best response confirmed by robwill100 (Occasional Contributor)
Solution

# Re: Excel formula query

=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?

# Re: Excel formula query

Thanks for your help, formula is working well.

Cheers