SOLVED

Excel formula query

%3CLINGO-SUB%20id%3D%22lingo-sub-3114593%22%20slang%3D%22en-US%22%3EExcel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3114593%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%20and%20thank%20you%20in%20advace%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20query%20I%20have%20is%20how%20to%20write%20a%20formula%20that%20searches%20column%20C%20(Channel%20Partner)%20for%20a%20match%20in%20column%20G%2C%20(Suppliers)%20when%20it%20finds%20a%20match%20it%20takes%20the%20data%20in%20Column%20H%20and%20inserts%20it%20in%20column%20E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%202nd%20query%20requires%20a%20formula%20that%20matches%20the%20ID%20number%20in%20column%20B%20with%20the%20ID%20Number%20is%20Column%20J%20and%20takes%20the%20corresponding%20invoice%20value%20data%20in%20Column%20K%20and%20puts%20it%20in%20column%20F%20on%20the%20same%20row%20as%20the%20matched%20ID%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20end%20result%20I%20am%20looking%20for%20is%20to%20take%202%20sets%20of%20data%20and%20merge%20them%20so%20I%20can%20allocate%20a%20Parner%20Abrev%20to%20each%20channel%20partne%20and%20then%20work%20out%20how%20much%20those%20with%20a%20partner%20abrev%20of%20%22BB%22%20%26amp%3B%20%22AA%22%20generate%20in%20invoice%20value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%20-%20Rob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3114593%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3115847%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3115847%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20want%20to%20do%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121880%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20quick%20response%20on%20this%20query.%3C%2FP%3E%3CP%3EI%20can%20see%20that%20the%20ID%20number%20in%20column%20B%20is%20matchimg%20with%20Column%20J%20and%20pulling%20the%20invoice%20value%20into%20column%20F%20which%20is%20what%20it%20should%20do.%3C%2FP%3E%3CP%3EThe%20only%20thing%20that%20appears%20not%20to%20be%20quite%20right%20is%20that%20the%20channel%20partner%20name%20in%20colum%20C%20should%20have%20the%20partner%20ID%20in%20Column%20E%20that%20is%20returned%20from%20the%20list%20in%20column%20H%20which%20for%20that%20particular%20channel%20partner.%3C%2FP%3E%3CP%3ETherefore%20the%20formula%20needs%20to%20take%20the%20data%20in%20C2%2C%20%22Mai%20Baker%22%20and%20seasrch%20for%20the%20correseponding%20name%20in%20Column%20G%20and%20return%20the%20correspopnding%20partner%20abbrev%20in%20Colimn%20H%20to%20the%20partner%20ID%20Column%3C%2FP%3E%3CP%3EThe%20above%20examples%20show%20that%20%22Mai%20Baker%22%20in%20C2%20should%20have%20a%20Partner%20id%20of%20AA%20in%20Column%20E%20however%20it%20is%20showing%20BB.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3122071%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122071%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%26nbsp%3BTry%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3122431%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(VLOOKUP(C2%2C%24G%242%3A%24H%2414%2C2%2CFALSE)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20the%20formula%20for%20column%20E%20(Partner%20ID)%20as%20shown%20above.%20This%20works%20in%20my%20sheet.%20Unfortunately%20i%20didn't%20understand%20in%20the%20beginning%20what%20you%20want%20to%20do.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20changes%20the%20values%20in%20columns%20L%20and%20M%20as%20well.%20Are%20the%20results%20in%20columns%20L%20and%20M%20what%20you%20finally%20want%20to%20do%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3146590%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3146590%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20help%2C%20formula%20is%20working%20well.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3C%2FLINGO-BODY%3E
Occasional 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?

@Quadruple_Pawn 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 (Occasional 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