get data from source table using index and match

[tehobeng]

Hi,

i have problem with my work. Dunno should i change my table form so i can run index and match function properly.
pls see attachment.

2nd, i have problem with pivot table,
i make pivot from a source data table, there are data column there.
data column in pivot table shows randomly dates. I tried to sort oldest to newest or newest to oldest, but nothing change.
so it looks like;

1-jun
10-jun
11-jun
3-jun

how can i sort correctly from 1 to 30 in a month.


thanks,

[Jacc]

Will this work for you?
Also check out this very similar problem i did just minutes ago: http://www.excelforum.com/excel-gene...x-matches.html

[tehobeng]

wow this is great Jacc, thx man.
i will try this formula tonight on my work... this is so great

but could you explain to me how this formula working???


terimakasih

[Jacc]

Glad to hear it works! By the way, these are array formulas, they need to be entered with Ctrl + Shift + Enter.

The IF around the COUNTIF is working because if COUNTIF returns 0 that is interpreted as FALSE and any other number is interpreted as TRUE. You can watch the formula calculate step by step by using the Evaluate Formula button in under the Formula tab. Try that first and then ask again if you don't understand.