Complex Function: search vertical column list to correspond numbers from another list

[Jeremy Forsythe]

Im running version 15.40

Let me try to simplify the function I need.

Imagine a vertical column of 500 random 3-digit numbers. So something like this...

123
231
354
394
457
534
543
644
744
873
... etc (500 data points in total)


I also have a smaller vertical column of 3-digit numbers (say 35 numbers in total) that correspond to some of the numbers in the larger set of 500 numbers. These 35 numbers come with additional data in 3 columns beside it. Looks something like this...

123...apple...blue...tree
354...orange...red...bush
457...banana...green...twig
644...pear...purple...grass

Note that the numbered column is in order from lowest to highest.

What I need to do is this: I need a function that will allow me to in one go match the smaller list of 3-digit numbers with the data beside it with its corresponding twin (the same 3-digit number) in the long sequence of 500 3-digit numbers, so that the sequence of 500 numbers is not affected but the data that goes with the smaller list is plugged into the larger sequence. After the function it will look like this...


123...123...apple...blue...tree
231
354...354...orange...red...bush
394
457...457...banana...green...twig
534
543
644...644...pear...purple...grass
744
873
etc to 500 data points


Basically, i need a function that will search the larger list and find the corresponding number and then attach the data from the smaller list with it with leaving the gaps. This function will save me days of work.


If there is no function like this then can you please suggest something?

Thank you.

[Jeremy Forsythe]

Here is the attachment that demonstrates the data before the function and the data after the function.

[jeffreybrown]

Hi Jeremey,

I'm not entirely sure what the relationship is between B3:B17 and then what's in F3:F7, but it D22 copied to the right and down...

=IFERROR(VLOOKUP($C22,$F$3:$I$7,COLUMN(B$1),0),"")

[jeffreybrown]

Or are you saying you want the results in C22:F36 in C3:F17?

If so, in C3 copied down >> =IF(ISNUMBER(MATCH($B3,$J$3:$J$7,0)),$B3,"")
In D3 copied to the right and down >> =IFERROR(VLOOKUP($C3,$J$3:$M$7,COLUMN(B$1),0),"")

[Jeremy Forsythe]

Perfect thank you! Worked perfectly!

[jeffreybrown]

You are very welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.