Lookup table help - multiple returns for one lookup value

**radddogg** · 09-14-2016, 12:40 PM

Hi,

I'm hoping you can help with a formula I'm trying to work out.

It is to help with supply and demand.

I have a table where the left column contains a part number, on another sheet I have an order book which shows a list of due in dates for the part numbers. I want to look up the part number against the order book and return the first due date for the part number in the first column, then in the second column show the next due date for the part number and so on.

I've attached an example. Can anyone advise?

Thanks

**6StringJazzer** · 09-14-2016, 01:07 PM

Here is a solution that adds a helper column to your Orderbook sheet.

**José Augusto** · 09-14-2016, 01:13 PM

Try this formula in B2 and copy down and right

Formula:

Please Login or Register  to view this content.

**Tony Valko** · 09-14-2016, 02:17 PM

This array formula** entered in B2:

=IFERROR(INDEX(Orderbook!$B:$B,SMALL(IF(Orderbook!$A$2:$A$22=$A2,ROW(Orderbook!A$2:A$22)),COLUMNS($B2:B2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

Copy across to H2 then down as needed.

**AlRicotta** · 09-14-2016, 08:07 PM

In addition to the above solutions, I might consider adding a column on the demand tab that counts the number of due dates if there can potentially be more than 7.
Then maybe a flag or conditional format to highlight any greater than 7...just to make sure all of your data is being accounted for.
Just a thought...

**FlameRetired** · 09-14-2016, 10:57 PM

Another way. Array-entered in B2 of 'demand' filled down and across (re-format for date).

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

**radddogg** · 09-15-2016, 04:24 AM

Hi,

Thanks so much for all the help so far. You guys are like wizards!

So there is an additional complexity which I didn't originally share as I hoped I could just nest your formula in my lookup.

What I have is a separate "Demand" sheet listing whether there is an open demand for a part number against the contract. I use a helper column to concatenate the contract number and part number and do a lookup from my "Supply" sheet which shows me if there is an open demand or whether the demand is complete
. I want the "Supply" table to check the "Demand" table and check whether there is a demand, if there is I want to reference the "orderbook" and fill in the open demands with the next available delivery date. There is one added complexity that are two contract types "SD" and "MA" and an "SD" part cannot be used on an "MA" contract (and vice versa).

I've attached an updated workbook which should provide the necessary raw data to accomplish the task. Thanks again.

I appreciate the help.

**radddogg** · 09-15-2016, 09:56 AM

I've been reading up and trying things out all day and I'm stumped

**AlRicotta** · 09-15-2016, 10:01 AM

Could you just pop it in a pivot table real quick?
You could even add month and year fields to filter it down.

**AlRicotta** · 09-15-2016, 10:03 AM

Parts number in rows.
Date in columns.
Parts number is values.

Put month/year filters if used.

**radddogg** · 09-15-2016, 10:15 AM

Hi,
I've tried a pivot but its not going to achieve what I want which is P/N down the left column, project number across the top, each corresponding cell showing if the demand has been fulfilled, if not, when is the next expected due date sequentially

**AlRicotta** · 09-15-2016, 10:58 AM

Oh sorry. I missed the updated file. I'll take another crack at it after lunch.

**radddogg** · 09-15-2016, 06:00 PM

Originally Posted by AlRicotta

Oh sorry. I missed the updated file. I'll take another crack at it after lunch.

Can anyone help please? Thanks

**AlRicotta** · 09-15-2016, 07:18 PM

I sent you a pm. I came up with a solution, but I can't upload the attachment for some reason.

**AlRicotta** · 09-15-2016, 07:49 PM

I think I figured out the attachment thing (sorry I'm new here).
Does this solution work for you?

**FlameRetired** · 09-15-2016, 08:59 PM

Not sure I've grasped the concept, yet.

There is a helper row (row 9) 'supply'. Formula in B9 and filled across

Formula:

Please Login or Register  to view this content.

Then this array-entered (Ctrl + Shift + Enter) formula in B12 filled down and across.

Formula:

Please Login or Register  to view this content.

Row\Col

A

B

C

D

E

F

G

H

I

J

K

L

9

SD

MA

10

Project

11

Part No

SD1

SD2

SD3

SD4

SD5

SD6

SD7

MA1

MA2

MA3

MA4

12

A091323

10/30/2016

11/15/2016

Complete

11/15/2016

Complete

13

A091336

Complete

14

A111145

1/1/2025

Complete

15

A130759

Complete

16

A130761

Complete

17

A131753

Complete

**sanram** · 09-15-2016, 09:04 PM

I know that's look uglier but this will solve your problem.
On B12 :

Please Login or Register  to view this content.

Now copy right and down.

**radddogg** · 09-16-2016, 04:23 AM

Originally Posted by AlRicotta

I think I figured out the attachment thing (sorry I'm new here).
Does this solution work for you?

Hi AlRicotta,

Thanks for your effort but I'm afraid it doesn't achieve what I'm looking for. The supply table with the conditional formatting looks up against the demand sheet and if it finds a match I need it to populate from left to right to show the next expected delivery date. I have manually entered the data in this attachment to show what I am trying to achieve. I've left in the previous attempts for comparison.

Thanks in advance

**radddogg** · 09-16-2016, 04:28 AM

Originally Posted by FlameRetired

Not sure I've grasped the concept, yet.

There is a helper row (row 9) 'supply'. Formula in B9 and filled across

Formula:

Please Login or Register  to view this content.

Then this array-entered (Ctrl + Shift + Enter) formula in B12 filled down and across.

Formula:

Please Login or Register  to view this content.

Row\Col

A

B

C

D

E

F

G

H

I

J

K

L

9

SD

MA

10

Project

11

Part No

SD1

SD2

SD3

SD4

SD5

SD6

SD7

MA1

MA2

MA3

MA4

12

A091323

10/30/2016

11/15/2016

Complete

11/15/2016

Complete

13

A091336

Complete

14

A111145

1/1/2025

Complete

15

A130759

Complete

16

A130761

Complete

17

A131753

Complete

Hi Dave,

Thank you, this is the closest so far but the demand lookup isn't accurate and I can't work out why.

Project
Part No SD1 SD2 SD3 SD4 SD5 SD6 SD7 MA1 MA2 MA3 MA4
A091323 Demand Demand Demand Demand Demand Demand Demand Demand Demand Demand Demand
A091336 Complete Complete Demand Complete Complete Complete Complete Complete Complete Demand Complete
A111145 Demand Demand Demand Demand Demand Complete Demand Demand Demand Complete Demand
A130759 Complete Complete Demand Complete Demand Complete Complete Complete Complete Demand Complete
A130761 Complete Complete Demand Demand Complete Demand Demand Complete Complete Demand Demand
A131753 Complete Complete Complete Demand Complete Complete Complete Complete Complete Complete Complete

I don't know how to paste the table in but the lookup should return the following demand picture.

Thanks

**radddogg** · 09-16-2016, 04:37 AM

Originally Posted by sanram

I know that's look uglier but this will solve your problem.
On B12 :

Please Login or Register  to view this content.

Now copy right and down.

Thank you Sanram, however when using your formula the cells are populated by either "Complete" or "Demand", whereas the demand cells should display the first date.

**radddogg** · 09-16-2016, 04:41 AM

For anyone looking at this, the attached file has a manually entered table displaying what I'm trying to achieve.

Thanks

**DannyJ** · 09-16-2016, 05:22 AM

Where it says "Demand" just change that to the cell reference you want Radddogg

**sanram** · 09-16-2016, 08:18 AM

Originally Posted by radddogg

Thank you Sanram, however when using your formula the cells are populated by either "Complete" or "Demand", whereas the demand cells should display the first date.

May be you are doing something wrong. See the image and attachment. It's producing dates and if there remain no available dates then it's showing Demand.

Untitled-1.jpg

**FlameRetired** · 09-16-2016, 02:10 PM

radddogg, you responded to Sanram:

Thank you Sanram, however when using your formula the cells are populated by either "Complete" or "Demand", whereas the demand cells should display the first date.

that's not what you indicated earlier.

The supply table with the conditional formatting looks up against the demand sheet and if it finds a match I need it to populate from left to right to show the next expected delivery date.

My proposal returns the next date.

**FlameRetired** · 09-16-2016, 02:22 PM

'Contract' in 'demand' sheet are format SD#, MA#. There are also no dates in 'demand' to make the connection to the dates in 'orderbook'.

'Contract' in 'orderbook' sheet are format SD, MA .... no numbers. There are dates in in 'orderbook'.

There seems to be some missing information. Can you "read us in" on how to make relationship connections?

Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Re: Lookup table help - multiple returns for one lookup value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Vlookup returns error even when Lookup Value and Value on the table is the same

[SOLVED] Lookup table returns #N/A

Bookmarks

Bookmarks

Posting Permissions