Trying to get values by cross referencing using functions

[ACIMarc]

HI,

I have a (currently) two worksheet workbook set up

On sheet 1, I have data from row 2:row 25 including values in cells C2:C:25 similar to this: C330698-9

On Sheet 2, I have a table which:
Has a Horizontal axis from cells B3:L3 contains the values 0 thru A {Hex}
Has a Vertical axis from cells A4:A24 contains the values 0 thru 20{Dec}
Contains Text data in the cells B4:L24


I am trying to create a formula which will be placed in cells Sheet 1!J2:J25
The intent of the formula is to use the INDEX and MATCH functions to
Pull the 5th value and the 10th value from the values in cells C2:C:25 using the RIGHT and MID functions
Use those two values to cross reference the table on sheet 2
Present the values recovered from the table in cells Sheet 1!J2:J25

Here is the formula I have in cell J2 now
=INDEX(Table3,MATCH((RIGHT(C2,1)),Sheet2!A4:A24,0),MATCH((MID(C2,5,1)),Sheet2!B3:L3,0))

But all I get in the cell is: #N/A

What am I doing wrong?
Thanks in advance!

Marc

[Tony Valko]

I think we'll need to see a sample file.

Make it as SMALL as possible!

[ACIMarc]

Here it is,
Designer.xlsx

It's not much as I was just getting started

Thanks for your help!

Marc

[Tony Valko]

A couple of things...

On Sheet2 the column headers in B3:K3 are TEXT values even though they look like numbers. Re-enter those values and they will convert to numbers.

On Sheet1 the data in column C... Some of the entries end with a number and some end with a letter. However, on Sheet2 A4:A24 only has numbers 0 to 20.

Try this array formula** entered in J2:

=INDEX(Table3,MATCH(RIGHT(C2),Sheet2!A3:A24&"",0),MATCH(--MID(C2,5,1),Sheet2!B3:L3,0)+1)

** 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.

[FDibbins]

Try this...
=INDEX(Table3,MATCH(--MID(C2,5,1),Sheet2!$A$3:$A$24,0),MATCH(RIGHT(C2,1),Sheet2!$B$3:$L$3,0))
(I think you had teh mathes the wrong way round?)

[ACIMarc]

OK,

I reformatted the data in Sheet 2!B3:K3 to numbers

As for the number/letter issue between the Column C and Sheet 2!A4:A24, this will all be in Hexadecimal eventually. I was trying to get the formula to work with the one line first before fighting the next battle

As for the array formula, I copied it as you wrote it, pasted it into the cell J2 and then hit CTRL+SHIFT+ENTER..

And still get the same results

[ACIMarc]

This actually gets the #value! error
Which is something different!

[FDibbins]

As for the number/letter issue between the Column C and Sheet 2!A4:A24, this will all be in Hexadecimal eventually.

You need to give us data that is representative of what you are working with. If your sample is diffenet to what you have, we will all just be guessing at what you need.

My formula gave this...

	A	B	C	D	E	F	G	H	I	J
1										PDB
2	Zeycude	101	C330698-9	Z	Na Ni Po De	613		Zh		3K
3	Reno	102	C1207B9-A		Na Po De	603		Zh		20K
4	Errere	103	B263664-B	Z	Ni Ri	910		Zh		#N/A
5	Cantrel	104	C366243-9		Lo Ni	520		Zh		5C

J2=INDEX(Table3,MATCH(--MID(C2,5,1),Sheet2!$A$3:$A$24,0),MATCH(RIGHT(C2,1),Sheet2!$B$3:$L$3,0))

I swapped your MATCHES around, based on you having a letter in the heading, but not in colunm A. The #N/A is because you have no B in the heading

[ACIMarc]

Wait!

I see part of the issue here.

The "A B C D..." and the "1 2 3 4.." are the standard edges of the spreadsheet
the only Characters in the sheet which will be acted on are in cells A2:C...

And "right now", the only data the function is trying to act on is Cell C2 [5] and [10]

So the char data should have no affect

Marc

[FDibbins]

Like I said, upload someththing that is actually representative of what you have - and show what you want, and how you got that

[ACIMarc]

Like I said, upload someththing that is actually representative of what you have - and show what you want, and how you got that

If you scroll back, you will see I actually uploaded the entire spreadsheet to this thread.
How much more "representative of what you have" can I get?

As for what I want, using this specific data..
Where MID(C2,5,1) Yields 6 and RIGHT(C2) yields 9,
...Where the horizontal (top) axis = 6
...and the vertical (side) axis = 9
the cross reference is Cell H13, so the displayed value should be "15", which is contained in Cell H13

[FDibbins]

If you scroll back, you will see I actually uploaded the entire spreadsheet to this thread.
How much more "representative of what you have" can I get?

If you mean the file in post #3...

Here it is,
Designer.xlsx

then my request stands, if there is an attachment in another post, my apologies, I must have missed it.

[Tony Valko]

so the displayed value should be "15", which is contained in Cell H13

That's the result I get with the formula I suggested.

[FDibbins]

Based on this logic...

Where MID(C2,5,1) Yields 6 and RIGHT(C2) yields 9,
...Where the horizontal (top) axis = 6
...and the vertical (side) axis = 9

this is the reason I said it looked like you have the 2 references swapped around? The next row down gives 7 A...column 7/row A, but you only have letters across the top, not down the side?

[ACIMarc]

That's the result I get with the formula I suggested.

Huh,

I put the formula you gave me in and now get #VALUE!
So I must have done something wrong.

Step by Step, I:
- copied from your post
- double clicked on the cell and hit control C
- Hit CTRL+ SHIFT + ENTER

and I get #VALUE!

So what am I doing wrong?

[ACIMarc]

If you mean the file in post #3...


then my request stands, if there is an attachment in another post, my apologies, I must have missed it.

Yes, that's it.
What more can I say to describe what I'm doing then give you the actual work?

[ACIMarc]

Based on this logic...


this is the reason I said it looked like you have the 2 references swapped around? The next row down gives 7 A...column 7/row A, but you only have letters across the top, not down the side?

That's why I posted the entire spread sheet.
This way you could see the formulas and what they actually referred to rather than guessing at what I am saying.

As I said in a later post, "at the moment" the formula "Only" evaluates the data in Cell C2 and the table. The "7 A" data you refer to are in the Cell K3, and that column is there only because I used it to confirm my RIGHT and MID formula usage

Marc

[Tony Valko]

Huh,

I put the formula you gave me in and now get #VALUE!
So I must have done something wrong.

Step by Step, I:
- copied from your post
- double clicked on the cell and hit control C
- Hit CTRL+ SHIFT + ENTER

and I get #VALUE!

So what am I doing wrong?

Here's your file with the formula returning the correct result...

[Tony Valko]

P.S.

Did you enter the formula as an array formula?

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.

[ACIMarc]

Here's your file with the formula returning the correct result...

Thank you.
Now I have to figure out where I went wrong, but I at least have something to compare to :D


Marc

[Tony Valko]

Good deal. Thanks for the feedback!