Home

Can we use VLookup to fill in fields from Scattered Data ?

%3CLINGO-SUB%20id%3D%22lingo-sub-558158%22%20slang%3D%22en-US%22%3ECan%20we%20use%20VLookup%20to%20fill%20in%20fields%20from%20Scattered%20Data%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-558158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20wanted%20to%20know%20that%20is%20it%20possible%20to%20use%20VLookup%20to%20fill%20in%20data%20in%20a%20certain%20row%20by%20matching%20it%20from%20different%20rows%20rather%20than%20just%20from%20a%20single%20row.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIn%20the%20attached%20image%2C%20I%20want%20to%20fill%20in%20the%20cells%20next%20to%20%22Yellow%22%20i.e.%20(H4)%20%26amp%3B%20%22Pink%22%20i.e.%20(H7)%20by%20using%20the%20same%20formula%20I%20used%20to%20in%20H2%20cell%20which%20is%20%22%3DVLOOKUP(G2%3AG7%2CA2%3AE12%2C2%2C0)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%22.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENOTE%20-%20That%20I%20have%20selected%20the%20table%20array%20from%20%22A2%3AE12%22%20which%20covers%20the%20values%20of%20D11%2C%20E11%20%26amp%3B%20D12%2C%20E12%20in%20which%20the%20%22Yellow%22%20and%20%22Pink%22%20information%20is%20given.%20I%20do%20realise%20that%20the%20source%20has%20become%20different%20now%2C%20but%20is%20there%20a%20way%20in%20which%20I%20can%20fill%20in%20H4%20%26amp%3B%20H7%20through%20ANY%20FORMULA%20(if%20Vlookup%20can't%20be%20used)%20where%20I%20don't%20have%20to%20restructure%20the%20data%20in%20the%20sheet.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20You%20in%20advance.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-558158%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EHLOOKUP%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Elookup%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELookup%20Column%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evlookup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560687%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20we%20use%20VLookup%20to%20fill%20in%20fields%20from%20Scattered%20Data%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560687%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339974%22%20target%3D%22_blank%22%3E%40siddharthbeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20several%20ways%20to%20solve%20this.%3C%2FP%3E%3CP%3ETwo%20examples%3A%3C%2FP%3E%3CPRE%3E%3DSUMPRODUCT(--(%24A%242%3A%24D%2412%3DG2)%2C%24B%242%3A%24E%2412)%3C%2FPRE%3E%3CPRE%3E%3DIFERROR(VLOOKUP(G2%2C%24A%242%3A%24B%2412%2C2%2CFALSE)%2CVLOOKUP(G2%2C%24D%242%3A%24E%2412%2C2%2CFALSE))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20best%20option%20is%20to%20restructure%20the%20data%20so%20that%20you%20can%20use%20just%20one%20VLOOKUP().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562275%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20we%20use%20VLookup%20to%20fill%20in%20fields%20from%20Scattered%20Data%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562275%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20both%20methods%20but%20none%20of%20them%20is%20able%20to%20pull%20the%20values%20of%20Pink%20%26amp%3B%20Yellow.%26nbsp%3B%20In%20the%20first%20method%2C%20the%20value%20is%20always%20coming%20to%20be%20%222500%22%20and%20in%20the%20second%20method%20it's%20only%20filling%20in%20%22TRUE%22%20and%20no%20the%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20-%20I'm%20attaching%20the%20file%20where%20the%20problem%20is.%20Please%20have%20a%20look%20at%20it%20and%20tell%20me%20if%20you%20have%20a%20way%20to%20solve%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562378%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20we%20use%20VLookup%20to%20fill%20in%20fields%20from%20Scattered%20Data%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339974%22%20target%3D%22_blank%22%3E%40siddharthbeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20why%20do%20still%20use%20the%20wrong%20VLOOKUP()%20and%20get%20a%20mixture%20of%20two%20formulas%20in%20one%20column.%3C%2FP%3E%3CP%3EThere%20has%20to%20be%20only%20one%20formula%20in%20the%20column.%20Put%20in%20H2%20and%20copy%20it%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572958%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20we%20use%20VLookup%20to%20fill%20in%20fields%20from%20Scattered%20Data%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572958%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20put%202%20formulas%20you%20gave%20me%20separately.%20I'm%20attaching%20the%20file%2C%20you%20can%20view%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575847%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20we%20use%20VLookup%20to%20fill%20in%20fields%20from%20Scattered%20Data%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575847%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339974%22%20target%3D%22_blank%22%3E%40siddharthbeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20there%20is%20only%20one%20cell%20with%20a%20formula%20and%20it%20is%20not%20correct.%3C%2FP%3E%3CP%3EFirst%20you%20moved%20the%20data%20from%20column%20G%20to%20column%20F%20and%20second%20the%20formula%20is%20in%20G7%20and%20references%20the%20criteria%20in%20G2%20-%20which%20now%20makes%20no%20sense.%3C%2FP%3E%3CP%3EThe%20correct%20formula%20for%20-%20now%20-%20G3%20is%3A%3C%2FP%3E%3CPRE%3E%3DSUMPRODUCT(--(%24A%242%3A%24D%2412%3DF3)%2C%24B%242%3A%24E%2412)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
siddharthbeam
New Contributor

Hi,

I wanted to know that is it possible to use VLookup to fill in data in a certain row by matching it from different rows rather than just from a single row.

In the attached image, I want to fill in the cells next to "Yellow" i.e. (H4) & "Pink" i.e. (H7) by using the same formula I used to in H2 cell which is "=VLOOKUP(G2:G7,A2:E12,2,0)
".

 

NOTE - That I have selected the table array from "A2:E12" which covers the values of D11, E11 & D12, E12 in which the "Yellow" and "Pink" information is given. I do realise that the source has become different now, but is there a way in which I can fill in H4 & H7 through ANY FORMULA (if Vlookup can't be used) where I don't have to restructure the data in the sheet.

 

Thank You in advance.

5 Replies

@siddharthbeam 

There are several ways to solve this.

Two examples:

=SUMPRODUCT(--($A$2:$D$12=G2),$B$2:$E$12)
=IFERROR(VLOOKUP(G2,$A$2:$B$12,2,FALSE),VLOOKUP(G2,$D$2:$E$12,2,FALSE))

 

But the best option is to restructure the data so that you can use just one VLOOKUP().

 

@Detlef Lewin

 

I tried both methods but none of them is able to pull the values of Pink & Yellow.  In the first method, the value is always coming to be "2500" and in the second method it's only filling in "TRUE" and no the value.

 

P.S. - I'm attaching the file where the problem is. Please have a look at it and tell me if you have a way to solve it.

 

Thank You in advance.

@siddharthbeam 

I don't know why do still use the wrong VLOOKUP() and get a mixture of two formulas in one column.

There has to be only one formula in the column. Put in H2 and copy it down.

@Detlef Lewin 

 

I did put 2 formulas you gave me separately. I'm attaching the file, you can view it.

@siddharthbeam 

Now there is only one cell with a formula and it is not correct.

First you moved the data from column G to column F and second the formula is in G7 and references the criteria in G2 - which now makes no sense.

The correct formula for - now - G3 is:

=SUMPRODUCT(--($A$2:$D$12=F3),$B$2:$E$12)

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies