vlookup with multiple source and target columns

[asrivera]

hi all.. i need help regarding vlookup using multiple columns and looking up also multiple target columns.

i think i can do this using nested IFERRORs, but im hoping for a more elegant solution.

i attached a file for example and also a screenshot.

Capture.JPG

[FDibbins]

You have attached the file (thanks), and said what you think you need to use - but you forgot to tell us WHAT you are trying to do?
I have a feeling you need to use INDEX/MATCH

Which table is data, and which 1 is output?
I also suggest that you use the exact same headings in both tables

[asrivera]

hi FDibbins..

given the two tables, i need to vlookup the qty column (output) on the left table from the right table (data table).
im having problems due to multiple columns on both tables.

You have attached the file (thanks), and said what you think you need to use - but you forgot to tell us WHAT you are trying to do?
I have a feeling you need to use INDEX/MATCH

Which table is data, and which 1 is output?
I also suggest that you use the exact same headings in both tables

[Pete_UK]

Do you mean that the Site Name columns (A to D) could be either in Site a1 or Site a2 columns ?

It would help if you gave some idea of what you are expecting - please manually mock up some expected results and re-attach your workbook.

Pete

[asrivera]

Yes Mr. Pete.. that's what i mean..

i have two tables from different sources, and i have to fill in the qty column (yellow) using the qty column from the 2nd table.

Capture.JPG

[avk]

Since in column "H" & "I" no duplicate > use in "E3"

=SUMPRODUCT(($H$3:$H$19=A3)*($H$3:$H$19=B3)*($J$3:$J$19))+SUMPRODUCT(($I$3:$I$19=C3)*($I$3:$I$19=D3)*($J$3:$J$19))

[KOKOSEK]

=SUMPRODUCT(($H$3:$H$19=A3)*($H$3:$H$19=B3)*($J$3:$J$19))+SUMPRODUCT(($I$3:$I$19=C3)*($I$3:$I$19=D3)*($J$3:$J$19))

What if value from col. B appear in col. I? Or from col. 3 in col. H?

[asrivera]

Since in column "H" & "I" no duplicate > use in "E3"

=SUMPRODUCT(($H$3:$H$19=A3)*($H$3:$H$19=B3)*($J$3:$J$19))+SUMPRODUCT(($I$3:$I$19=C3)*($I$3:$I$19=D3)*($J$3:$J$19))

thanks avk, but did not work.. also, it's possible to have duplicates in columns H & I, but i can easily remove if required.

Capture.JPG

[KOKOSEK]

If you can create help column G like:

=H3&I3

and put something like (into E3)

=IFERROR(INDEX(J:J,MATCH("*"&INDEX(A3:D3,MATCH(1,COUNTIF($G$3:$G$19,"*"&A3:D3&"*"),0))&"*",G:G,0)),"")

accept with CSE and drag it down

Not tested so intensively.

[asrivera]

If you can create help column G like:

=H3&I3

and put something like (into E3)

=IFERROR(INDEX(J:J,MATCH("*"&INDEX(A3:D3,MATCH(1,COUNTIF($G$3:$G$19,"*"&A3:D3&"*"),0))&"*",G:G,0)),"")

accept with CSE and drag it down

Not tested so intensively.

Thanks Kokosek.. but also did not work.. nothing matched.

Capture.JPG

[KOKOSEK]

Thanks Kokosek.. but also did not work.. nothing matched.

Attachment 614274

Hmm weird. Check attached file. IMHO works fine.
Did you accept formula with CSE?

[asrivera]

Hmm weird. Check attached file. IMHO works fine.
Did you accept formula with CSE?

CSE!! i had to google that.. and now it works!! thanks so much! now i will play around with more columns

[azumi]

Maybe put this on E3 and copied down as necessary

=IFERROR(IFERROR(IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE($H$3:$H$19," ","*"),$D3),$J$3:$J$19),LOOKUP(1000,SEARCH(LEFT($H$3:$H$19,FIND(" ",$H$3:$H$19&" ")-1),$D3),$J$3:$J$19)),INDEX($J$3:$J$19,MATCH(C3,$I$3:$I$19,0))),"")

[asrivera]

Maybe put this on E3 and copied down as necessary

=IFERROR(IFERROR(IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE($H$3:$H$19," ","*"),$D3),$J$3:$J$19),LOOKUP(1000,SEARCH(LEFT($H$3:$H$19,FIND(" ",$H$3:$H$19&" ")-1),$D3),$J$3:$J$19)),INDEX($J$3:$J$19,MATCH(C3,$I$3:$I$19,0))),"")

Thanks Azumi.. but also not working.. the formula is no longer matching if i remove some cells from the working table.

Capture.JPG

[asrivera]

i am actually able to do it with 4x2=8 nested iferror and vlookup.. but it's very crude and just plain ugly.. and also a pain to adjust for more columns.

[KOKOSEK]

You welcome

[asrivera]

hi kokosek.. i think i found a bug in your formula..

the values in yellow are not going as expected, not matching and partial matching..
kindly check? thanks.

Capture.JPG

[KOKOSEK]

Change G3 (helper) into:

=CHAR(160)&H3&CHAR(160)&I3&CHAR(160)

and change E3 into:

=IFERROR(INDEX(J:J,MATCH("*"&INDEX(A3:D3,MATCH(1,COUNTIF($G$3:$G$19,"*"&CHAR(160)&A3:D3&CHAR(160)&"*"),0))&"*",G:G,0)),"")

accept with Ctrl+Shift+Enter (an drag it down as needed).

Capture.JPG

[asrivera]

works perfectly! thanks so much kokosek!

[KOKOSEK]

You welcome .