Discussion Can we use VLookup to fill in fields from Scattered Data ? in Excel
https://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/558158#M30789
<P><SPAN>Hi,</SPAN><BR /><BR /><SPAN>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.</SPAN><BR /><BR /><SPAN>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)</SPAN><BR /><SPAN>".</SPAN></P><P> </P><P><SPAN>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.</SPAN></P><P> </P><P><SPAN>Thank You in advance.</SPAN></P>Fri, 10 May 2019 12:32:03 GMTsiddharthbeam2019-05-10T12:32:03ZCan we use VLookup to fill in fields from Scattered Data ?
https://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/558158#M30789
<P><SPAN>Hi,</SPAN><BR /><BR /><SPAN>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.</SPAN><BR /><BR /><SPAN>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)</SPAN><BR /><SPAN>".</SPAN></P><P> </P><P><SPAN>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.</SPAN></P><P> </P><P><SPAN>Thank You in advance.</SPAN></P>Fri, 10 May 2019 12:32:03 GMThttps://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/558158#M30789siddharthbeam2019-05-10T12:32:03ZRe: Can we use VLookup to fill in fields from Scattered Data ?
https://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/560687#M30816
<P><LI-USER uid="339974"></LI-USER> </P><P>There are several ways to solve this.</P><P>Two examples:</P><PRE>=SUMPRODUCT(--($A$2:$D$12=G2),$B$2:$E$12)</PRE><PRE>=IFERROR(VLOOKUP(G2,$A$2:$B$12,2,FALSE),VLOOKUP(G2,$D$2:$E$12,2,FALSE))</PRE><P> </P><P>But the best option is to restructure the data so that you can use just one VLOOKUP().</P><P> </P>Sat, 11 May 2019 10:21:09 GMThttps://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/560687#M30816Detlef Lewin2019-05-11T10:21:09ZRe: Can we use VLookup to fill in fields from Scattered Data ?
https://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/562275#M30894
<P><LI-USER uid="1639"></LI-USER></P><P> </P><P>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><P> </P><P>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.</P><P> </P><P>Thank You in advance.</P>Mon, 13 May 2019 05:13:09 GMThttps://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/562275#M30894siddharthbeam2019-05-13T05:13:09ZRe: Can we use VLookup to fill in fields from Scattered Data ?
https://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/562378#M30902
<P><LI-USER uid="339974"></LI-USER> </P><P>I don't know why do still use the wrong VLOOKUP() and get a mixture of two formulas in one column.</P><P>There has to be only one formula in the column. Put in H2 and copy it down.</P>Mon, 13 May 2019 06:19:07 GMThttps://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/562378#M30902Detlef Lewin2019-05-13T06:19:07ZRe: Can we use VLookup to fill in fields from Scattered Data ?
https://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/572958#M31111
<P><LI-USER uid="1639"></LI-USER> </P><P> </P><P>I did put 2 formulas you gave me separately. I'm attaching the file, you can view it.</P>Thu, 16 May 2019 07:38:10 GMThttps://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/572958#M31111siddharthbeam2019-05-16T07:38:10ZRe: Can we use VLookup to fill in fields from Scattered Data ?
https://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/575847#M31146
<P><LI-USER uid="339974"></LI-USER> </P><P>Now there is only one cell with a formula and it is not correct.</P><P>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.</P><P>The correct formula for - now - G3 is:</P><PRE>=SUMPRODUCT(--($A$2:$D$12=F3),$B$2:$E$12)</PRE><P> </P>Thu, 16 May 2019 17:09:06 GMThttps://techcommunity.microsoft.com/t5/excel/can-we-use-vlookup-to-fill-in-fields-from-scattered-data/m-p/575847#M31146Detlef Lewin2019-05-16T17:09:06Z