VLOOKUP matching on more than one condition

J

JadonR

Board Regular
Joined
Apr 15, 2009
Messages
154
Office Version
  1. 365
Hey guys. I've used VLOOKUP's in the past but this seems a bit more complicated than that. Essentially I would like to use a formula to populate Column 'H' on the sheet named UPCS Export (see highlighted field in first image below) with the value in the 'K' column from the IM_BARCOD sheet (see second image below). The difficult part is that it needs to also match on two other columns. The values in UPCS Export, Column 'B' should match the number in IM_BARCOD, Column A. It should also match UPCS Export, Column 'E' on IM_BARCOD, Column 'C'. If there is NO MATCH, then the number in Column H should be incremented in sequence based on the highest number. See last image for more details.

1681489254358.png


1681489288280.png


So below you can see that there is a match between the first 5 rows (non-highlighted in below image). The match should be connected between sheet IM_BARCOD, Column A and sheet UPCS Export Column B. It also needs to match between IM_BARCOD, Column C and sheet UPCS Export Column E.

The next 10 do NOT MATCH (highlighted) so those should look at the highest value in sheet IM_BARCOD, Column K for the corresponding range of numbers in Column A. In the example below Sheet IM_BARCOD has number 38518 in Column Awith the highest sequencial number ending in 22. So it should assign 23 through 32.

1681489649554.png


I didn't even attempt a VLOOKUP as I know I couldn't come close. Any help would be appreciated.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
thanks for all your detailed images. Images are really difficult for the forum to create your scenario accurately and quickly. The xl2bb add in (link below) is a tool that you can post mini worksheets to share with the forum. The images are helpful in point to what you need help with, but the typos and time consumption are challenges the forum faces.

Also, please tell the forum what version of excel you use (Please update your profile with this information so it appears in your user button). This is because many forum members may propose solutions that will not work in your version, it is very important again in saving time and effort for the people that want to provide you with a solution.

if you cannot intall the add in, then posting some regular tables will give the forum the data to start with, as well.
 
Upvote 0
So, you want to lookup Column B and Column E values in columns A and C respectively and return column K?
since I cannot make the worksheet i may have a typo or to. But you can concatentate your lookups and and lookup column.

try this:
Assuming formula is in cell H2:

Excel Formula: 
=INDEX(IM_BARCODE!$K$2:$K$100000, match($B2 & $E2,    IM_BARCODE!$A$2:$A$100000  & IM_BARCODE!$E$2:$E$100000,  0))
 
Upvote 0
That formula didn't work and prompted to open a file. I'm using Office 365 Pro Plus Version 1908. I've installed the addin so I could attach a mini-sheet.

CP Export Datasheet 1st t-shirt PO colors added.xlsm
ABCDEFGH
1BARCODITEM_NOBARCOD_IDUNITDIM_1_UPRDIM_2_UPRDIM_3_UPRSEQ_NO
219296420451538518UPC0WHI-WhiteSGrid31
319296420454638518UPC0WHI-WhiteM2
419296420457738518UPC0WHI-WhiteL3
519296420460738518UPC0WHI-WhiteXL4
619296420463838518UPC0WHI-WhiteXXL5
719569978684438518UPC0BURD-Burl RedS6
819569978689938518UPC0BURD-Burl RedM7
919569978696738518UPC0BURD-Burl RedL8
1019569978705638518UPC0BURD-Burl RedXL9
1119569978714838518UPC0BURD-Burl RedXXL10
1219569979416038504UPC0DMGO-Dri MangoS11
1319569979426938504UPC0DMGO-Dri MangoM12
1419569979433738504UPC0DMGO-Dri MangoL13
1519569979440538504UPC0DMGO-Dri MangoXL14
1619569979448138504UPC0DMGO-Dri MangoXXL15
1719296418949238504UPC0WHI-WhiteS16
1819296418950838504UPC0WHI-WhiteM17
1919296418952238504UPC0WHI-WhiteL18
2019296418954638504UPC0WHI-WhiteXL19
2119296418956038504UPC0WHI-WhiteXXL20
2219296418553138504UPC0CNY-Clssc navyS21
2319296418555538504UPC0CNY-Clssc navyM22
2419296418557938504UPC0CNY-Clssc navyL23
2519296418559338504UPC0CNY-Clssc navyXL24
2619296418561638504UPC0CNY-Clssc navyXXL25
UPCS Export
Cell Formulas
RangeFormula
A2A2=Table1[@UPC]
B2B2=Table1[@[Style Number]]
D2D2='Color Codes'!C1
E2E2=VLOOKUP(Table1[@[Color Code]],'Color Codes'!A$3:B$1000,2,FALSE)
F2F2=Table1[@Size]
G2G2=IF(Table1[@[Alt Size]]=0,"",Table1[@[Alt Size]])
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACell ValueduplicatestextNO


CP Export Datasheet 1st t-shirt PO colors added.xlsm
ABCDEFGHIJK
1ITEM_NOBARCOD_IDDIM_1_UPRDIM_2_UPRDIM_3_UPRBARCODUNITLST_MAINT_DTLST_MAINT_USR_IDLST_LCK_DTSEQ_NO
238518UPCWHI-WHITEXS*19296420448506/30/2022 15:07SSC1
338518UPCWHI-WHITES*19296420451506/30/2022 15:07SSC2
438518UPCWHI-WHITEM*19296420454606/30/2022 15:07SSC3
538518UPCWHI-WHITEL*19296420457706/30/2022 15:07SSC4
638518UPCWHI-WHITEXL*19296420460706/30/2022 15:07SSC5
738518UPCWHI-WHITEXXL*19296420463806/30/2022 15:07SSC6
838518UPCCNY-CLASSC NAVYXS*19296419911806/30/2022 15:07SSC7
938518UPCCNY-CLASSC NAVYS*19296419912506/30/2022 15:07SSC8
1038518UPCCNY-CLASSC NAVYM*19296419913206/30/2022 15:07SSC9
1138518UPCCNY-CLASSC NAVYL*19296419914906/30/2022 15:07SSC10
1238518UPCCNY-CLASSC NAVYXL*19296419915606/30/2022 15:07SSC11
1338518UPCCNY-CLASSC NAVYXXL*19296419916306/30/2022 15:07SSC12
1438518UPCBARR-BARN REDXS*19296460926606/30/2022 15:07SSC13
1538518UPCBARR-BARN REDS*19296460927306/30/2022 15:07SSC14
1638518UPCBARR-BARN REDM*19296460929706/30/2022 15:07SSC15
1738518UPCBARR-BARN REDL*19296460931006/30/2022 15:07SSC16
1838518UPCBARR-BARN REDXL*19296460933406/30/2022 15:07SSC17
1938518UPCBARR-BARN REDXXL*19296460935806/30/2022 15:07SSC18
2038518UPCGLH-GRAVEL HTRXS*19296420628106/30/2022 15:07SSC19
2138518UPCGLH-GRAVEL HTRS*19296420630406/30/2022 15:07SSC20
2238518UPCGLH-GRAVEL HTRM*19296420632806/30/2022 15:07SSC21
2338518UPCGLH-GRAVEL HTRL*19296420633506/30/2022 15:07SSC22
2438518UPCGLH-GRAVEL HTRXL*19296420634206/30/2022 15:07SSC23
2538518UPCGLH-GRAVEL HTRXXL*19296420635906/30/2022 15:07SSC24
2638518UPCBLK-BLACKXS*19296419869206/30/2022 15:07SSC25
2738518UPCBLK-BLACKS*19296419870806/30/2022 15:07SSC26
2838518UPCBLK-BLACKM*19296419872206/30/2022 15:07SSC27
2938518UPCBLK-BLACKL*19296419874606/30/2022 15:07SSC28
3038518UPCBLK-BLACKXL*19296419876006/30/2022 15:07SSC29
3138518UPCBLK-BLACKXXL*19296419877706/30/2022 15:07SSC30
3238518UPCCTRB-CRATER BLUS*19296460543506/30/2022 15:07SSC31
3338518UPCCTRB-CRATER BLUM*19296460547306/30/2022 15:07SSC32
3438518UPCCTRB-CRATER BLUL*19296460551006/30/2022 15:07SSC33
3538518UPCCTRB-CRATER BLUXL*19296460555806/30/2022 15:07SSC34
3638518UPCCTRB-CRATER BLUXXL*19296460559606/30/2022 15:07SSC35
3738518UPCRHP-ROSEHIPXS*19418756599206/30/2022 15:07SSC36
3838518UPCRHP-ROSEHIPS*19418756625806/30/2022 15:07SSC37
3938518UPCRHP-ROSEHIPM*19418756655506/30/2022 15:07SSC38
4038518UPCRHP-ROSEHIPL*19418756686906/30/2022 15:07SSC39
4138518UPCRHP-ROSEHIPXL*19418756717006/30/2022 15:07SSC40
4238518UPCRHP-ROSEHIPXXL*19418756749106/30/2022 15:07SSC41
4338518UPCBABN-BSALT BRWNS*19418755253406/30/2022 15:07SSC42
4438518UPCBABN-BSALT BRWNM*19418755257206/30/2022 15:07SSC43
4538518UPCBABN-BSALT BRWNL*19418755261906/30/2022 15:07SSC44
4638518UPCBABN-BSALT BRWNXL*19418755266406/30/2022 15:07SSC45
4738518UPCBABN-BSALT BRWNXXL*19418755270106/30/2022 15:07SSC46
4838518UPCSEGN-SEDGE GRNS*19418757471006/30/2022 15:07SSC47
4938518UPCSEGN-SEDGE GRNM*19418757520506/30/2022 15:07SSC48
5038518UPCSEGN-SEDGE GRNL*19418757568706/30/2022 15:07SSC49
5138518UPCSEGN-SEDGE GRNXL*19418757617206/30/2022 15:07SSC50
5238518UPCMOKH-MORAY KHAS*19569912050106/30/2022 15:07SSC51
5338518UPCMOKH-MORAY KHAM*19569912087706/30/2022 15:07SSC52
5438518UPCMOKH-MORAY KHAL*19569912123206/30/2022 15:07SSC53
5538518UPCMOKH-MORAY KHAXL*19569912161406/30/2022 15:07SSC54
5638518UPCMOKH-MORAY KHAXXL*19569912199706/30/2022 15:07SSC55
5738518UPCWAVB-WAVY BLUEXS*19569913771406/30/2022 15:07SSC56
5838518UPCWAVB-WAVY BLUES*19569913794306/30/2022 15:07SSC57
5938518UPCWAVB-WAVY BLUEM*19569913816206/30/2022 15:07SSC58
6038518UPCWAVB-WAVY BLUEL*19569913841406/30/2022 15:07SSC59
6138518UPCWAVB-WAVY BLUEXL*19569913871106/30/2022 15:07SSC60
6238518UPCWAVB-WAVY BLUEXXL*19569913901506/30/2022 15:07SSC61
6338519UPCPTPL-PITON PURPM*192964597327010/23/2020 9:45SS21
6438519UPCPTPL-PITON PURPS*192964597303010/23/2020 9:45SS22
IM_BARCOD
 
Upvote 0
It is looking for the sheet named IM_BARCODE... You had that in the initial post, and I advised I may have typos.
Thanks for posting the add in. I'll see what I can do.
JadonR said:
Here is the prompt I get when pasting the formula into cell H2
Click to expand...
 
Upvote 0
Okay, my typo was in IM_BARCODE vs IM_BARCOD.
The above would have worked with that correction. So, I have that in the column H below, and I 've moved your column H to column I for comparison purposes. But as you will notice they don't match. So, I added a 3rd column with a third criteria in the formula to match for the SIZE. That seems to work a little better.

Also I don't know what your table references in row 2 mean, other than maybe you are also looking up from another source to build that worksheet.
So, here you go, please let us know if you have other questions:

mr excel questions 24.xlsm
ABCDEFGHIJ
1BARCODITEM_NOBARCOD_IDUNITDIM_1_UPRDIM_2_UPRDIM_3_UPRSEQ_NO
2=Table1[@UPC]=Table1[@[Style Number]]UPC#REF!=VLOOKUP(Table1[@[Color Code]],'Color Codes'!A$3:B$1000,2,FALSE)=Table1[@Size]=IF(Table1[@[Alt Size]]=0,"",Table1[@[Alt Size]])1triple match (added size lookup)
319296420454638518UPC0WHI-WhiteM123
419296420457738518UPC0WHI-WhiteL134
519296420460738518UPC0WHI-WhiteXL145
619296420463838518UPC0WHI-WhiteXXL156
719569978684438518UPC0BURD-Burl RedS#N/A6#N/A
819569978689938518UPC0BURD-Burl RedM#N/A7#N/A
JadonR
Cell Formulas
RangeFormula
D2D2='Color Codes'!C1
H3:H8H3=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(JadonR!B3&JadonR!E3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64,0))
J3:J8J3=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(JadonR!B3&JadonR!E3&F3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64&IM_BARCOD!$D$2:$D$64,0))
 
Upvote 0
if you want to use a blank or put some text in the cells that are N/A# you can wrap the formulas i've given in a IFERROR statement:
=IFERROR( 'the formula you want to use', "") or
=IFERROR( 'the formula you want to use', "ITEM NOT FOUND")
 
Upvote 0
Thank you! I used the formula you provided but for some reason it is inserting my Forum username into the formula and doesn't work properly.

=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(JadonR!B3&JadonR!E3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64,0))
=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(JadonR!B3&JadonR!E3&F3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64&IM_BARCOD!$D$2:$D$64,0))

I removed the part in bold but it still doesn't work. I also attempted to replace my username with the sheet name.

=INDEX(IM_BARCOD!$K$2:$K$64,MATCH('UPCS Export'!B2&'UPCS Export'!E2&F2,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64&IM_BARCOD!$D$2:$D$64,0))

Either way I get a formula error. I'm obviously doing something wrong...


CP Export Datasheet 1st t-shirt PO colors added.xlsm
ABCDEFGHI
1BARCODITEM_NOBARCOD_IDUNITDIM_1_UPRDIM_2_UPRDIM_3_UPRSEQ_NO
219296420451538518UPC0WHI-WhiteSGrid3#NAME?#VALUE!
319296420454638518UPC0WHI-WhiteM2
419296420457738518UPC0WHI-WhiteL3
519296420460738518UPC0WHI-WhiteXL4
619296420463838518UPC0WHI-WhiteXXL5
719569978684438518UPC0BURD-Burl RedS6
819569978689938518UPC0BURD-Burl RedM7
919569978696738518UPC0BURD-Burl RedL8
1019569978705638518UPC0BURD-Burl RedXL9
1119569978714838518UPC0BURD-Burl RedXXL10
1219569979416038524UPC0DMGO-Dri MangoS11
1319569979426938524UPC0DMGO-Dri MangoM12
1419569979433738524UPC0DMGO-Dri MangoL13
1519569979440538524UPC0DMGO-Dri MangoXL14
1619569979448138524UPC0DMGO-Dri MangoXXL15
1719296418949238524UPC0WHI-WhiteS16
1819296418950838524UPC0WHI-WhiteM17
1919296418952238524UPC0WHI-WhiteL18
2019296418954638524UPC0WHI-WhiteXL19
UPCS Export
Cell Formulas
RangeFormula
A2A2=Table1[@UPC]
B2B2=Table1[@[Style Number]]
D2D2='Color Codes'!C1
E2E2=VLOOKUP(Table1[@[Color Code]],'Color Codes'!A$3:B$1000,2,FALSE)
F2F2=Table1[@Size]
G2G2=IF(Table1[@[Alt Size]]=0,"",Table1[@[Alt Size]])
H2H2=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(B2&E2E3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64,0))
I2I2=INDEX(IM_BARCOD!$K$2:$K$64,MATCH('UPCS Export'!B2&'UPCS Export'!E2&F2,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64&IM_BARCOD!$D$2:$D$64,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACell ValueduplicatestextNO


Maybe something to do with these two sheets?
1681733630633.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Time differences
Replies
1
Views
95
Jwinssss
J
N
  • Question
Match and lookup combined
Replies
4
Views
166
Glenn Kennedy
G
A
  • Question
Xlookup, Vlookup and/or VBA for finding, matching and inputting data to separate sheet
Replies
0
Views
513
AlfredDB
A
E
  • Solved
This one is tricky. VLookup but return a different cell value
Replies
6
Views
137
Alex Blakenburg
A
S
  • Question
Repeating a pivot table many times and changing only one column -- are there more efficient approaches?
Replies
0
Views
147
sschaefer
S

Forum statistics

Threads
1,214,606
Messages
6,120,483
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top