Hi guys,
I have a simple index(matrix function but it does not return the desired value. It seems to combine the lookup values in a way.
For
grade 11 step 01 it returns value from
grade 01 step 11, for
grade 11 02 it returns value from
grade 01 step 12 and so on
The simplified file is attached.
Does anyone have an idea?
Many thanks!
Last edited by JasXel; 02-08-2018 at 06:59 AM.
yes you do matches on the numbers not the formats
a1 is 1 and b1 is 11 so you match 111 for your first match which is in row 3, if you make the first 2 columns text, not numbers they should work
You have formatted cells that's why the problem occures
try below array formula in E8 and copy towards down
=INDEX(C:C,MATCH(TEXT(A8,"00")&TEXT(B8,"00"),TEXT(A$1:A$100,"00")&TEXT(B$1:B$100,"00"),0))
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thanks a lot! This worked!Originally Posted by nflsales
I still do not understand the problem. In the formula I say look for 11 in column A and for 01 in column B. Excel returns value for 01 from column A and 10 from column B. Why is that? Even if my number format is always 2 digits, leaving the leading 0 out it still makes no sense. I say look for 11 in A and 1 in B and excel returns 1 from A and 10 from B.. HUH? Why?
Thanks!
An alternative would be to deconcatenate your MATCH, like this ordinary formula:
=INDEX($C$2:$C$12,MATCH(1,INDEX(($A$2:$A$12=A8)*($B$2:$B$12=B8),),0))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
On iPad so can't see your formula. However, it sounds as though you have your MATCH functions the wrong way round. The function is =INDEX(range, row, column). I think you have =INDEX(range, column, row)
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
if you see in cell A2 to A7 are looks like 01 but actually they are 1 s you can see the value of cell A2 in the formula ba.
so when you combines A3&B3 the value is 1&11 i,e 111 like that for A8&B8 the value is 11&1 i,e 111 hence A3&B3 = A8&B8
so that you format picks the wrong value.
When you concatenate the columns... row 3 is read as 1+11=111. Row 8 is read as 11+1 = 111. Index returns the value from C corresponding to the first 111 that it sees. You need to ensure that the extra digits, added by your formatting, are retained to get the correct result. That is what nflsales' formula does.
One way:
=INDEX($C$1:$C$100,MATCH(A8&"|"&B8,INDEX($A$1:$A$100&"|"&$B$1:$B$100,0),0))
Last edited by Phuocam; 02-08-2018 at 06:44 AM.
OK, I understood now (PUH) Thanks everyone!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks