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

123.JPG

P.S. - The data I shared in the image is very small, but if I figure it out then I can apply to large data which is similar to this problem.

Thank You in advance.

Does this work?

Formula:

Please Login or Register  to view this content.

Hi,

I have inserted that value in cell "H4" & "H6" but It's filling in incorrect value of "2500" in both of them.