Lookup on multiple values (separated by space) then return values to multiple columns

V

Vouge

New Member
Joined
Nov 21, 2010
Messages
8
good evening everyone :)

I have my query as Lookup on multiple value (separated by space) then return values to multiple columns:

Here's my sheet 1:

Column A ---------------------Column B--Column C--Column D
Data -------------------------Invoice 1 - Invoice 2 - Invoice 3
531351 861351 516835 ----------- ?--------- ? --------?

Here's on my sheet 2:
Column A -- Column B
Invoice------- Data
Inv50551 --- 531351
Inv50552 --- 861351
Inv50553 --- 516835

I would like to return the looked up values to Invoice 1, 2, 3 columns

I hope I have give the problem clearly and Thhhhhhhhhhhhaaaaaaaaaaannkkkkkkkkkk you so much excel genuises for your kind help :p
 
Last edited:
Sort by date Sort by votes
Perfect Mike but I have tried to enter some duplicated values and it choses the first one:

Sheet1

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 141px;"><col style="width: 61px;"><col style="width: 141px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="background-color: rgb(255, 204, 0);">Details</td><td style="background-color: rgb(255, 204, 0);">Count</td><td style="background-color: rgb(255, 204, 0);">Invoice 1</td><td style="background-color: rgb(255, 204, 0);">Invoice 2</td><td style="background-color: rgb(255, 204, 0);">Invoice 3</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>531351 861351 516835</td><td style="text-align: right;">3</td><td>Inv50551</td><td>Inv50552</td><td>Inv50553</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right;">143445254</td><td style="text-align: right;">1</td><td>Inv50554</td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>Reference</td><td>Invoice</td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="text-align: left;">531351</td><td>Inv50551</td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="text-align: left;">861351</td><td>Inv50552</td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td style="text-align: left;">516835</td><td>Inv50553</td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="text-align: left;">14344</td><td>Inv50554</td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="text-align: left;">143445254</td><td>Inv50556</td><td> </td><td> </td><td> </td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B2</td><td>=IF(LEN(A2),LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1,0)</td></tr><tr><td>C2</td><td>{=IF(COLUMNS($C2:C2)>$B2,"",INDEX($B$9:$B$13,SMALL(IF(ISNUMBER(SEARCH($A$9:$A$13,$A2)),ROW($A$9:$A$13)-ROW($A$9)+1),COLUMNS($C2:C2))))}</td></tr><tr><td>D2</td><td>{=IF(COLUMNS($C2:D2)>$B2,"",INDEX($B$9:$B$13,SMALL(IF(ISNUMBER(SEARCH($A$9:$A$13,$A2)),ROW($A$9:$A$13)-ROW($A$9)+1),COLUMNS($C2:D2))))}</td></tr><tr><td>E2</td><td>{=IF(COLUMNS($C2:E2)>$B2,"",INDEX($B$9:$B$13,SMALL(IF(ISNUMBER(SEARCH($A$9:$A$13,$A2)),ROW($A$9:$A$13)-ROW($A$9)+1),COLUMNS($C2:E2))))}</td></tr><tr><td>B3</td><td>=IF(LEN(A3),LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ",""))+1,0)</td></tr><tr><td>C3</td><td>{=IF(COLUMNS($C3:C3)>$B3,"",INDEX($B$9:$B$13,SMALL(IF(ISNUMBER(SEARCH($A$9:$A$13,$A3)),ROW($A$9:$A$13)-ROW($A$9)+1),COLUMNS($C3:C3))))}</td></tr><tr><td>D3</td><td>{=IF(COLUMNS($C3:D3)>$B3,"",INDEX($B$9:$B$13,SMALL(IF(ISNUMBER(SEARCH($A$9:$A$13,$A3)),ROW($A$9:$A$13)-ROW($A$9)+1),COLUMNS($C3:D3))))}</td></tr><tr><td>E3</td><td>{=IF(COLUMNS($C3:E3)>$B3,"",INDEX($B$9:$B$13,SMALL(IF(ISNUMBER(SEARCH($A$9:$A$13,$A3)),ROW($A$9:$A$13)-ROW($A$9)+1),COLUMNS($C3:E3))))}</td></tr></tbody></table></td></tr><tr><td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>


 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Here is a Excel 2007 or later formula that "might" work:

Excel Workbook
ABCDEFG
1DataCount SpacesInvoicesInvoicesInvoicesInvoicesInvoices
2531351 861351 5168353Inv50551Inv50552Inv50553..
314344 1434452542Inv50556Inv50554...
40.....
5
6
7ReferenceInvoice
8531351Inv50551
9861351Inv50552
10516835Inv50553
11143445254Inv50554
1214344Inv50556
2007-10
[/B]





Formula in B2:B4:

Excel Workbook
B
23
2007-10
Cell Formulas
RangeFormula
B2=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1*(LEN(A2)<>0)



Excel Workbook
C
2Inv50551
2007-10
Cell Formulas
RangeFormula
C2=IF(COLUMNS($C2:C2)>$B2,"",INDEX($B$8:$B$12,MATCH(LEFT(REPLACE(TRIM(SUBSTITUTE(" "&$A2&" "," ","^",COLUMNS($C2:C2))),1,SEARCH("^",TRIM(SUBSTITUTE(" "&$A2&" "," ","^",COLUMNS($C2:C2)))),"")&" ",SEARCH(" ",REPLACE(TRIM(SUBSTITUTE(" "&$A2&" "," ","^",COLUMNS($C2:C2))),1,SEARCH("^",TRIM(SUBSTITUTE(" "&$A2&" "," ","^",COLUMNS($C2:C2)))),"")&" "))+0,$A$8:$A$12,0)))


In Excel 2003 or earlier, there are too many nested functions so you may have to break it apart a bit.

I hope one of the true Excel Masters at this awesome site can come up with a better solution. This was a really tough question.

If I were required to do this and I was pressed for time, I would not waste the effort creating a formula, I would just do Text To Columns, Then do a simple formula like =IF(cell with Text To Columns value="","",INDEX(range with invoices, MATCH(cell with Text To Columns value, range with reference numbers,0))). Then Paste Special Values and delete the Text To Columns entries. Or something like that.
 
Upvote 0
In Excel 2003, "maybe":

Excel Workbook
ABCDEFG
1DataCount SpacesInvoicesInvoicesInvoicesInvoicesInvoices
2531351 861351 5168353Inv50551Inv50552Inv50553..
314344 1434452542Inv50556Inv50554...
40.....
5
6Data Extract:
7531351861351516835#VALUE!#VALUE!
814344143445254#VALUE!#VALUE!#VALUE!
9#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
10
11ReferenceInvoice
12531351Inv50551
13861351Inv50552
14516835Inv50553
15143445254Inv50554
1614344Inv50556
2003




Excel Workbook
B
23
2003
Cell Formulas
RangeFormula
B2=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1*(LEN(A2)<>0)



Excel Workbook
C
2Inv50551
2003
Cell Formulas
RangeFormula
C2=IF(COLUMNS($C2:C2)>$B2,"",INDEX($B$12:$B$16,MATCH(C7,$A$12:$A$16,0)))



Excel Workbook
C
7531351
2003
Cell Formulas
RangeFormula
C7=LEFT(REPLACE(TRIM(SUBSTITUTE(" "&$A2&" "," ","^",COLUMNS($C2:C2))),1,SEARCH("^",TRIM(SUBSTITUTE(" "&$A2&" "," ","^",COLUMNS($C2:C2)))),"")&" ",SEARCH(" ",REPLACE(TRIM(SUBSTITUTE(" "&$A2&" "," ","^",COLUMNS($C2:C2))),1,SEARCH("^",TRIM(SUBSTITUTE(" "&$A2&" "," ","^",COLUMNS($C2:C2)))),"")&" "))+0
 
Upvote 0
Text To Column Method:
1) highlight range A2:A4
2) Alt + D + E
3) Select Delimited, then Next
4) Select Space, then Finish

Then something like this:
Excel Workbook
ABCDEFG
1Data
2531351861351516835Inv50551Inv50552Inv50553
314344143445254Inv50556Inv50554
4
5
6ReferenceInvoice
7531351Inv50551
8861351Inv50552
9516835Inv50553
10143445254Inv50554
1114344Inv50556
...




formula entered in E2, then copy through E2:G3:
Excel Workbook
E
2Inv50551
...
Cell Formulas
RangeFormula
E2=IF(A2="","",INDEX($B$7:$B$11,MATCH(A2,$A$7:$A$11,0)))
 
Upvote 0
wow, i have not noticed you have replied here back. however, i already used your first given formula which is working good.

I really appreciate your reply here. I thank you so much.
 
Upvote 0
No problem, it took me a few days to get back to your relpy!
 
Upvote 0
You must log in or register to reply here.

Similar threads

megera716
  • Question
MAXIFS to take the MAX of two columns
Replies
1
Views
119
shift-del
shift-del
A
  • Question
Sumif / vlookup question
Replies
1
Views
159
RobP
R
K
  • Solved
Help with Identifying Most Recent Date In Another Sheet Based on Specific Criteria
Replies
3
Views
237
ktran2024
K
F
  • Question
Lookup assistance needed please
Replies
1
Views
206
Alex Blakenburg
A
K
  • Solved
last occurrence of a value in a 4 column array
Replies
4
Views
192
kmr47
K

Forum statistics

Threads
1,216,157
Messages
6,129,195
Members
449,493
Latest member
JablesFTW

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