VLOOKUP AND CHOOSE to find a selection

[Kurt]

Hello All,

I think this is the way to go with getting my values to return

In cells L2:L8 I have this:

BASE BBB XBB BXB BBX XXB XBX BXX XXX
000 000 500 050 005 550 505 055 555
001 001 501 051 006 551 506 056 556
002 002 502 052 007 552 507 057 557
003 003 503 053 008 553 508 058 558
004 004 504 054 009 554 509 059 559
010 010 510 060 015 560 515 065 565

The list is 128 rows long and the same size so I shortened for posting here.

In Cell K2 I want to use something like:

=VLOOKUP(find 010), CHOOSE (L3:L8) and return the value to cell U2 and copy all but the BASE number since it is repeated in cell L3 in other words copy L2:L8 values that may be found to destinatin cell U2:U7.

Also is there a way to do this with only one value at a time or possibly through a macro several number values?

I hope I have given a better clarification.  If you need me to clarify some more please let me know.

Thanks for any and all help in advance!!

Kurt

 

[Kurt]

I am now trying VLOOKUP using this line of code in cell U2

=VLOOKUP(J2, L3:T128, L3:L128,FALSE)

I am getting a #N/A error.

Can anyone help me out here?

 

[Kurt]

Here is one I am trying now:

=IF(C3="","",CHOOSE(MATCH(VLOOKUP(A3,L3:T128,L3:L123,FALSE)

I am getting the too few arguments error.

 

[scottylad2]

Here is one I am trying now:

=IF(C3="","",CHOOSE(MATCH(VLOOKUP(A3,L3:T128,L3:L123,FALSE)

I am getting the too few arguments error.

I'm not quite getting what your trying to acheive. If your only looking up a single item from the left most column and your only looking that up in a single table, why are you using choose? the #NA error in an earlier post is possivly due to the fact you've put the same table reference twice, the too few arguments error you quote above is relating to the match, the Vlookup will only return a single item, so from your syntax your looking up a single item for the match but you haven't shown a table array for the match,

It may help if you try stating what you are looking up, highlight what the output result should be.

 

[Kurt]

Hello Scottylad2,

Thanks for your reply.

I am trying to output a line like this 000 500 050 005 550 505 055 555 in column U2 if there is a match in the BASE column.

I hope this makes some more sense. I am going to start working with Excel Jeanie some today to help me show things more clearly.

Any ideas?

 

[scottylad2]

Is this what you need? the iferror is in Xl 2007 and later, if your versions earlier then you can use an alternative

Excel Workbook
	B	C	D	E	F	G	H	I	J
2	BASE	BBB	XBB	BXB	BBX	XXB	XBX	BXX	XXX
3	0	0	500	50	5	550	505	55	555
4	1	1	501	51	6	551	506	56	556
5	2	2	502	52	7	552	507	57	557
6	3	3	503	53	8	553	508	58	558
7	4	4	504	54	9	554	509	59	559
8	10	10	510	60	15	560	515	65	565
9
10
11	BASE	BBB	XBB	BXB	BBX	XXB	XBX	BXX	XXX
12	1	501	51	6	551	506	56	556
Sheet2

 

[Kurt]

I think that is almost it.

Where do I input the number that I want it to return?

I copied and pasted your formula into cell B15. How do I get it to return a value?

Also is there a way to insert multiple values?

I think we are almost there!

 

[scottylad2]

Put the value you are looking up in where I have B12

Vlookup will look up single items, you can "join" two diverent values to look up using the & but it can get complicated

 

[Kurt]

It changes the number in cell B2 now but not the following:

BBB XBB BXB BBX XXB XBX BXX XXX
501 51 6 551 506 56 556

This part is staying the same.

What else do I need to change.

Thanks for your help so far on a Saturday!