Look up column A based on column B, C, and D data

**wy52** · 03-05-2019, 11:28 AM

Hello,

I currently have two tabs, one tab is used as a user input page while data are in tab 2.
In tab 2 I have column A as parts, and column B, C, and D, as the dimensions of the part (width, length, depth).
On tab 1, I want the user to put in a width, depth, and height, and output the recommended part(s). The dimensions can be approximate and rounded.
I did
=IF(VLOOKUP(B2,CARTONS!B:B,2,TRUE),CARTONS!A:A,"No Components Availiable")
but got a #N/A as the output.

Please help, what am I doing wrong?

Thanks in advance

**modytrane** · 03-05-2019, 11:44 AM

please attach a sample file with typical data and enter expected result [manually].
It's difficult to help you without seeing actual sheet.

**Sam Capricci** · 03-05-2019, 11:45 AM

for one, this part CARTONS!B:B,2,TRUE is saying that you look at cartons tab in col B and then GO OVER to col C BUT you only list col B in as the distance over, in other words this needs to be CARTONS!B:C,2,TRUE
then the TRUE means that you are looking for an approximate match, not an exact match, is that what you want?
To go deeper you might need to post a sample workbook with the desired results. Hope that helps.

**wy52** · 03-05-2019, 12:04 PM

I've attached the file. Basically I need it to look up a drawing based on the dimensions given. The input width, depth and height doesn't have to be exact, and can be off by 0.015

Thanks again guys!

**Sam Capricci** · 03-05-2019, 12:11 PM

this appears to work...
=INDEX(CARTONS!$A$2:$A$6,MATCH('COMPONENT LOOKUP'!B2,CARTONS!$B$2:$B$6,0),MATCH('COMPONENT LOOKUP'!B3,CARTONS!$C$2:$C$6,0),MATCH('COMPONENT LOOKUP'!B4,CARTONS!$D$2:$D$6,0))

**wy52** · 03-05-2019, 12:21 PM

It does! Appreciate it Sambo kid. Is it possible to make it consider B3 and B4? For example, if they didn't put anything in B3 or B4, it wouldn't matter, but if the user did, it'll refine the search. This will be in case there is two of similar width, or similar depth (so they use height as well).
Or if it can be done, can it be made so that the order don't matter in case the user put width as depth, and depth as width?

Thanks,

**Sam Capricci** · 03-05-2019, 12:30 PM

this should take care of it if you have width, but no depth, width and depth but no height or if you have all three.
=IFERROR(INDEX(CARTONS!$A$2:$A$6,MATCH(B2,CARTONS!$B$2:$B$6,0)),IFERROR(INDEX(CARTONS!$A$2:$A$6,MATCH(B3,CARTONS!$C$2:$C$6,0)),NDEX(CARTONS!$A$2:$A$6,MATCH(B4,CARTONS!$D$2:$D$6,0))))

**wy52** · 03-05-2019, 12:35 PM

Thanks for the quick response! I tried it but when the first data is similar, it doesn't show up the correct answer. I've attached an updated spreadsheet with added data with similar width, and different length and depth.

Thanks,

**Sam Capricci** · 03-05-2019, 12:39 PM

make the zeros ones instead...
=IFERROR(INDEX(CARTONS!$A$2:$A$10,MATCH(B2,CARTONS!$B$2:$B$10,1)),IFERROR(INDEX(CARTONS!$A$2:$A$10,MATCH(B3,CARTONS!$C$2:$C$10,1)),INDEX(CARTONS!$A$2:$A$10,MATCH(B4,CARTONS!$D$2:$D$10,1))))
this will allow approximate matches.

**wy52** · 03-05-2019, 12:45 PM

ohh gotcha.
But now it doesn't show C-1509 when I put in 3.328, 1.75, 1.75.

**Sam Capricci** · 03-05-2019, 12:48 PM

it does on mine, see the attached workbook, cell B11.

**wy52** · 03-05-2019, 01:14 PM

Your attached file don't have the extra data. It didn't work when I try looking up other parts.

**Sam Capricci** · 03-05-2019, 02:01 PM

this is the only one I see that can work if you have one, two or three measures and it works only if there is an exact match...
=IFERROR(INDEX(CARTONS!$A:$A,MATCH(B2,CARTONS!$B:$B,0)),IFERROR(INDEX(CARTONS!$A:$A,MATCH(B3,CARTONS!$C:$C,0)),NDEX(CARTONS!$A:$A,MATCH(B4,CARTONS!$D:$D,0))))
Maybe someone else can figure out a way to give you approximate matches based on one, two or three criteria.

**wy52** · 03-05-2019, 03:09 PM

thanks for your help Sambo Kid! appreciate the help!

Can anyone else help with what I'm trying to do?

**modytrane** · 03-05-2019, 04:59 PM

Here's a simple macro that looks up based on three dimensions.
See attached file.

Does this work for you?

Please Login or Register  to view this content.

**modytrane** · 03-06-2019, 09:41 AM

If you need to match dimensions approximately, then you can define the range.
For example, you can say that if the dimensions are within 5% it's considered a match.
Compare statements in the macro can be modified to add that.

Look up column A based on column B, C, and D data

LinkBack

Thread Tools

Rate This Thread

Display

Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Re: Look up column A based on column B, C, and D data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VB Code to copy paste data from column to another column based on content of cell

Auto-adjust # of rows in a column that have a formula based on adjacent column data?

trouble to find two column data and comparing other columns based on the inital column

[SOLVED] Data extraction to new column from one column based on another column values

[SOLVED] count the number of populated cells in column N based on data in column D

Extract data from column B based on data in column A and 'paste' into column D

Merge two workbooks. Copy column data based on numerical ID match of another column.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions