VLOOKUP Formula Help

[Mr_Inc]

Hello,

I have a Data Table that returns a value based on how the column and row intersects, i.e. Y; Y 1; Y 1, 4. I am using the INDEX and MATCH formula's to return an answer, but when I use VLOOKUP to match the answer to the legend table I have created that explains the short hand, it only will recognize it if it only returns one value. I have tried using LEFT, MID and RIGHT will no luck, also tried SEARCH and FIND with in the VLOOKUP formula. Just wondering if there is something I am overlooking in my formula to make it work.

sample legend is

Y- Yes it can be transfered
1- Requires paperwork
4- Document must be notarized

Any help would be greatly appreciated

 

[jim may]

Can you post enough of a sample of your data that we can better understand what you are dealing with?

 

[Mr_Inc]

Here is a small section chart:

IRA /Roth IRA /Minor IRA /Minor Roth IRA/ Coverdell ESA / 529
UGMA-UTMA Y 8 Y 8 Y 8 Y 8 Y 8 Y 8
Minor IRA Y 9 C or B Y C or B N N
Minor Roth IRA R Y 9 R Y N N
Coverdell ESA N N N N Y Y 8

Legend Sample

Y The transfer is allowed per IRS Regulations.
N The transfer is NOT allowed.
B Decedents IRA or QRP is transferred into the receiving Beneficiary IRA.
C Conversion allowed of cash and/or stock with completed IRA Distribution Request Form.
8 Paperwork requires signature guaranty
9 Paperwork requires notary

I can't get the chart to line up on the post, but basically you have your top heading and side heading, depending on the account type your trying to consolidate with, determines if its allow and if so what additions steps need to be taken. I have it to where it pulls the match, an once there I am trying have it display the response in another cell. So "Y 8" would return a cell of "The transfer is allowed per IRS Regulations, Paperwork requires signature guaranty".

 

[jim may]

When I try Importing your chart - Can you see the confusion that results?
Download the MrExcel HTML Maker Software (see reference to in my signature line). Use it to post your chart, THEN we can help you!!

Jim

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	IRA	Roth IRA	Minor IRA	Minor Roth IRA	Coverdell ESA	529
2	UGMA-UTMA	Y	8	Y	8	Y	8	Y	8	Y	8	Y
3	Minor	IRA	Y	9	C	or	B	Y	C	or	B	N
4	Minor	Roth	IRA	R	Y	9	R	Y	N	N
5	Coverdell	ESA	N	N	N	N	Y	Y	8
Sheet1

Excel 2007

 

[Mr_Inc]

Excel Workbook
	C	D	E	F	G	H	I	J
25	From the Internal account below	.. to ==>	IRA	Roth IRA	Minor IRA	Minor Roth IRA	Coverdell ESA	529
26	UGMA/UTMA	==>	Y 8	Y 8	Y 8	Y 8	Y 8	Y 8
27	Minor IRA	==>	Y 9	C or R	Y	C or R	N	N
28	Minor Roth IRA	==>	R	Y 9	R	Y	N	N
29	Coverdell ESA	==>	N	N	N	N	Y	Y 8
Within

Excel 2007

 

[jim may]

Here is a SAMPLE Layout of maybe what you want - hope it helps...

Jim

Excel Workbook
	C	D	E	F	G	H	I	J	K	L
25	From the Internal account below	.. to ==>	IRA	Roth IRA	Minor IRA	Minor Roth IRA	Coverdell ESA	529
26	UGMA/UTMA	==>	Y 8	Y 8	Y 8	Y 8	Y 8	Y 8
27	Minor IRA	==>	Y 9	C or R	Y	C or R	N	N
28	Minor Roth IRA	==>	R	Y 9	R	Y	N	N
29	Coverdell ESA	==>	N	N	N	N	Y	Y 8
30
31	If (Enter Coordinates below)
32
33	UGMA/UTMA	<< Do a Drop Down of Row Choices
34	Roth IRA	<< Do a Drop Down of Column Choices
35
36	This Results to:
37	Y 8	The transfer is allowed per IRS Regulations, Paperwork requires signature guaranty								<<<<<
38
39
40	Set up a Table of Values to Report back
41
42	Results	Comment To Report
43	C or R
44	N
45	R
46	Y
47	Y 8	The transfer is allowed per IRS Regulations, Paperwork requires signature guaranty
48	Y 9
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C37	=INDEX($E$26:$J$29,MATCH($C$33,$C$26:$C$29,0),MATCH($C$34,$E$25:$J$25,0))
D37	=VLOOKUP(C37,C43:D48,2,FALSE)

 

[Mr_Inc]

Jim thanks for your help. Thats the original formula I created with VLOOKUP. It only works if I list all the possible combination. I was hoping to create a formula to look for each part of the answer and return the result. If thats not possible. How can I separate the result cell C37 and send each letter and/or number to separate cells. From there I can make VLOOKUPs for each cell.