Highlighted
New Contributor

# 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 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.

5 Replies
Highlighted

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

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

Highlighted

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

@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.

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

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.

Highlighted

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

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

Highlighted

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

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)`