Forum Discussion

siddharthbeam's avatar
siddharthbeam
Copper Contributor
May 10, 2019

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

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 ...
excel
Formulas and Functions
HLOOKUP
lookup
Lookup Column
vlookup
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
May 11, 2019

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().

 

siddharthbeam's avatar
siddharthbeam
Copper Contributor
May 13, 2019

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.

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    May 13, 2019

    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's avatar
        Detlef_Lewin
        Silver Contributor
        May 16, 2019

        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)

         

Resources